Pivot Columns in Power BI

Pivot column is a feature available in Power Query which allows you turn rows to columns.

Basically, Power Query groups each unique values and performs an aggregation that you specify such as sum, max, min, count, average and so on.. for each value, and pivots the column into a new table.



Let see , how to turn rows into columns using pivot column feature in Power Query step by step.

You can see, here we have a sample dataset named Item_Sales.

Item Year Sold Quantity
Iphone 5s 2018 5060
Iphone 6 2018 1500
Iphone 7 2018 6006
Samsung A12 2018 1200
Samsung A02s 2018 4633
Samsung Galaxy F41 2018 6700
Iphone 12 Pro 2020 1500
Iphone 12 2020 3800
Iphone 5s 2019 1000
Iphone 6 2019 5645
Iphone 7 2019 4534
Samsung A12 2019 8573
Samsung A02s 2019 5454
Samsung Galaxy F41 2019 5654
Iphone 5s 2020 2356
Iphone 6 2020 1465
Iphone 7 2020 7854
Samsung A12 2020 3423
Samsung A02s 2020 4534
Samsung Galaxy F41 2020 9587

Lets pivot the Year based on Sold Quantity using a Pivot Column feature of Power Query.

In Ribbon bar, Inside Home tab, click on Transform data, as shown below.

 

Now you will see that a Power Query Editor Opens, there you can transform your data.

As we have to pivot the Year column so just select Year column first as shown below.




Now Inside the Transform tab, click on Pivot Column as shown below.

Once you click on Pivot Column, you will see a Pivot Column dialog box opens.

In Values column : Select a column from values column drop down list, here we have selected a Sold Quantity, as we want to get the total number of Sold Quantity of all Items by Year.

In Advance Option, Aggregate Value function : Specify an Aggregate Value function, as we want to see the total number of sold quantity so selected a SUM aggregate function for the calculation.



After that click on OK button, and you will see a rows is turned into columns.

Now you have a pivot table, which display the Total sum of Sold Quantity of all Items by Year.

You will also notice that there is a null values in pivot table for some Item in particular year, which means no sales recorded in this year for that particular item.

In case, if you want to sort the column order in pivot table as you can see after column 2018 the next column is 2020 instead of  column 2019.

As you can see, the column 2019 is the last column in pivot table which should have come before 2020.

So, just select column 2019 and then expand Move drop down, and click on Left.

Now you can see, all the year columns in Pivot table are in order.

If you have more columns then you can use similar way to sort the column order using Move feature. You can also move column to right, To beginning and To end.



SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 686 total views,  7 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.