Power Query: Pivot Problems
13 January 2021
Welcome to our Power Query blog. This week, I look at an example using a pivot.
I have some tent data, just for a change…
I want to reorganise this data into separate columns for price, quantity and discount. I extract the data to Power Query by using ‘From Table’ from the ‘Get & Transform’ section of the Data tab. I am only interested in the main table, not in the title at the top of the sheet.
I start by filling down on Tent so that all rows are populated. I can do this by selecting Tent and then right-clicking to select Fill and then Down.
I want to use ‘Pivot Columns’ from the Transform tab, as it will create new columns from the values in Property, which sounds like what I want.
I know the values for these columns are in Amounts, and that I don’t want to aggregate this data in any way.
When I try to pivot, I get an error, which was not the plan. I take a closer look at the problem.
The error message tells me that ‘There were too many elements in the numeration to complete the operation’. I can see that the error only occurs with tent type ‘Wedding’, so I delete the ‘Pivoted Column’ step and look again at my data.
I have two entries for tent type ‘Wedding’ with different Property values. Since Property is being pivoted, this is giving me duplicate errors. I need to distinguish between these two entries outside of the data being pivoted. I need a new column. I want to pull out the price of the tent and include that in my Tent column. I start by adding a conditional column from the ‘Add Column’ tab.
I only want the amount to appear in my new column if it is the price of the tent.
I can now fill down on this column as I did for Tent earlier.
I will merge the Tent and Price columns from the Transform tab. The merge from the Transform tab does not keep the original columns, which is what I want in this particular case.
I select both columns in the order I want them to appear, and create an appropriate separator.
Now I can distinguish between the tents, so I pivot Property again.
This time I get my data in a format I can use.
Come back next time for more ways to use Power Query!