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:
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.
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.
I start by sorting out my supplier data, so I right click on Column1 and choose to ‘Fill Down’.
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.
On the Transform tab, I can select Column1 and Column2 and choose to ‘Merge Columns’.
I choose to separate my data by a colon (:), and use a meaningful name for my column name.
On the Transform tab, I also have the option to ‘Transpose’ my data, which will treat columns as rows and rows as columns.
When I click ‘Transpose’, my data is swapped around:
Next, I need to fill down the data in my first column so that ‘Rented’ or ‘Sold’ appears next to each month.
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.
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.
I can now split the Attribute column into my original columns.
I choose to split by colon.
I can now rename my columns.
I remove my total rows by filtering on Supplier, looking for the word ‘Total’.
I choose ‘Does Not End With…’ just to reduce my chances of coinciding with an actual supplier name.
I click ‘OK’ to see my data.
I reorder the columns.
I can now use ‘Close & Load’ on the ‘Home’ tab to upload my data to Excel.
Finally, I need to add data for July to my original Excel spreadsheet to check my query still works.
I refresh my query and check the data.
The data for July has been included.
Come back next time for more ways to use Power Query!