Power Query: Picky Pivoting
23 September 2020
Welcome to our Power Query blog. This week, I look at unpivoting data with multiple headings.
I have some tent data:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I want to get this data into a standard table format so that I can analyse it and combine it with other data. I want to do this in a dynamic way, so that if more months are added to my Excel sheet, then the data will be transformed correctly.
I start by extracting the data to Power Query by selecting ‘From Table’ in the ‘Get & Transform’ section of the Data tab.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
Power Query automatically selects my data without the ‘Grand Total’ line, and this is fine for my purposes. I choose not to select the ‘My table has headers’ box as it wouldn’t find the correct headers in any case.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I start by sorting out my supplier data, so I right click on Column1 and choose to ‘Fill Down’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I know that I want to keep the supplier data and the tent type data, so I choose to combine these columns. The remaining columns will need more manipulation to sort out the headings.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
On the Transform tab, I can select Column1 and Column2 and choose to ‘Merge Columns’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I choose to separate my data by a colon (:), and use a meaningful name for my column name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
On the Transform tab, I also have the option to ‘Transpose’ my data, which will treat columns as rows and rows as columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
When I click ‘Transpose’, my data is swapped around:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
Next, I need to fill down the data in my first column so that ‘Rented’ or ‘Sold’ appears next to each month.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
Now, I can promote the row with the supplier and tent type information to be my headers from the ‘Use First Row as Headers’ section.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
The data is already looking much better. I am ready to unpivot the quantities. I select the first column (Rented / Sold), and the Supplier:Tent Type column and choose to unpivot the other columns. I can do this by right clicking with my columns selected.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image12.png/a1537847463e660a31158c8032525438.jpg)
I can now split the Attribute column into my original columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image13.png/917da985be13220165c8d2823e95344f.jpg)
I choose to split by colon.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I can now rename my columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I remove my total rows by filtering on Supplier, looking for the word ‘Total’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I choose ‘Does Not End With…’ just to reduce my chances of coinciding with an actual supplier name.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
I click ‘OK’ to see my data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
I reorder the columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
I can now use ‘Close & Load’ on the ‘Home’ tab to upload my data to Excel.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
Finally, I need to add data for July to my original Excel spreadsheet to check my query still works.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image21.png/bc31e9359fd69b42bc104de457d8f9f8.jpg)
I refresh my query and check the data.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2020/power-query/199/image22.png/059b7a0f2b398df09f943b811a5b3126.jpg)
The data for July has been included.
Come back next time for more ways to use Power Query!