Power Query: Pivot Problems

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!