Power Query: Even More Fun with (Un)Pivot
25 December 2019
Welcome to our Power Query blog. This week, I look at another example of unpivoting.
John, my imaginary salesperson, has been creative with his accounts yet again.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
This time, John has decided to use two layers of titles. This does not work well with Excel or Power Query tables, so I have some transforming to do. I’d like to see a column for months, a column for amount and a column for tips – that way it’s much easier to add future data.
I start by extracting the data into Power Query using the ‘From Table’ option on the ‘Get & Transform’ section of the ‘Data’ tab. Since there are two layers of titles, I opt not to set anything as the column headers at this point.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
This gives me all my data in columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
As last week, I will remove the ‘Changed Type’ step. It really doesn’t have much value since I have so much transforming to do, and it refers to specific column names – I need to allow for adding extra columns for other months.
= Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}})
I delete this step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I need to fill across my months so that each month is attached to the ‘Tips’ title as well as the ‘Amount’. However, there is no ‘Fill Across’ that I can use. I need to transform my data so that I can fill it.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
On the ‘Transform’ tab, I have the option to ‘Transpose’ which will swap rows for columns. I use this.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I can now right click on Column1 and ‘Fill Down’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
I want to create one column which contains all my heading information, so I choose to transform my data by merging Column1 and Column2.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I choose to use a space to separate my headings, but as long as I pick a separator, it doesn’t really matter what I use as long as it isn’t in either of the headings. I can remove the separator when I split the data later.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I transpose my data so that the headings are at the top.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I can now ‘Use First Row as Headers’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
The data is now in the same format as last week’s example. However, Power Query has added a ‘Changed Type’ step again:
= Table.TransformColumnTypes(#"Promoted Headers",{{" Expense Type", type text}, {"August Amount", Int64.Type}, {"August Tips", Int64.Type}, {"September Amount", Int64.Type}, {"September Tips", Int64.Type}, {"October Amount", Int64.Type}, {"October Tips", Int64.Type}, {"November Amount", Int64.Type}, {"November Tips", Int64.Type}})
This references the months, so I need to remove this step.
As I did last week, I need to avoiding referencing month names in the columns. To do this, I select the Expense Type column, and opt to unpivot the other columns.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image12.png/a1537847463e660a31158c8032525438.jpg)
The M code generated is:
= Table.UnpivotOtherColumns(#"Promoted Headers", {" Expense Type"}, "Attribute", "Value")
This is good because there is no mention of the month columns. The months, amounts and tips titles now appear as data under the Attribute column. I need to perform further transformations to sort out this column. I begin by splitting the Attribute column into the separate titles.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image13.png/917da985be13220165c8d2823e95344f.jpg)
I opt to use ‘Split Columns’ on the ‘Transform’ tab, and choose ‘By Delimiter’
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I choose to split at ‘Space’, since this is the delimiter I introduced earlier.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
I can rename Attribute.1 to Month. Attibute.2 contains my two other column headings, so I need to pivot this column by selecting it and using the ‘Pivot’ option on the ‘Transform’ menu.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image16.png/d082e3477129350b8a2a589156028e63.jpg)
I need to choose what to put in my new columns; they should contain the associated Value, since that contains the amount.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image17.png/a468c9c28195e8f18fdedfa59a0cf042.jpg)
Finally, I choose to order by month (just like last time), since that makes the data easier to read. I need to create a temporary column with month number in order to do this, as I don’t want the months in alphabetical order!
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image18.png/61935f470d8b9f21b7b7683ffca40895.jpg)
I create this column and sort by it.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image19.png/06909e3fbfc8faf87de7555c0b99e9d3.jpg)
I can now delete the Month Number column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/160/image20.png/173ef895b62ce391674d271b99c4d178.jpg)
My data is now ready to append and update if further months are added.
Come back next time for more ways to use Power Query!