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.
5,587 total views, 5 views today