Power Query: Self Merging
10 March 2021
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I rename my second and third columns to Tent Colour and Tent Price respectively.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I can now right-click and choose to ‘Remove Errors’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
Removing the errors removes the colours from this column:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
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’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image12.png/a1537847463e660a31158c8032525438.jpg)
I click ‘OK’ to see my new column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image13.png/917da985be13220165c8d2823e95344f.jpg)
I can now remove the null rows by filtering and choosing to ‘Remove Empty’ on Tent Colour (new).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
I can now remove my new column.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/223/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
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!