Please note javascript is required for full website functionality.
MVP

Blog

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!

Newsletter