Power Query: Self Merging

Welcome to our Power Query blog. This week, I look at a way to reorganise data in a table by merging with itself.

Just for a change, I have some tent data:

I want to have the description and price in separate columns. To this end, I begin by extracting my data to Power Query using ‘From Table/Range’ on the ‘Get & Transform’ section of the Data tab.

There are a number of ways to solve this, but for this example, I am going to merge the table with itself. I don’t need to create a copy of the table to do this.

Power Query will allow me to link my table to itself, as indicated by the Table1 (Current) in the table dropdown for the second table. I choose to use the Left Outer join, and I join on the Tent Size field, since I know this will always be unique in this table.

The name given to my new column is the name of the previous step since this is equivalent to the current table. I can then expand the table:

I only want to see Tent Colour/Price. I don’t need to use a prefix as I don’t have many columns, so it will be easy to distinguish between them.

I rename my second and third columns to Tent Colour and Tent Price respectively.

The Tent Price column is easy to sort out, so I modify this field first. I can just transform the column type to ‘Whole Number’, and this will give me errors for the colours.

I can now right-click and choose to ‘Remove Errors’.

Removing the errors removes the colours from this column:

I need to remove the prices from the Tent Colour column. I can do this by creating a new conditional column. Since I plan to compare Tent Colour and Tent Price, I start by transforming them to data type ‘Text’.

I create a new column which will be null if Tent Colour is the same as the value in Tent Price, otherwise it is set to Tent Colour.

I click ‘OK’ to see my new column.

I can now remove the null rows by filtering and choosing to ‘Remove Empty’ on Tent Colour (new).

I can now remove my new column.

I have my data in the required format. Next time, I will look at a nested merging problem.

Come back next time for more ways to use Power Query!