Power Query: Merging Muddles
24 February 2021
Welcome to our Power Query blog. This week, I look at how to merge data when there is not a clear link between the tables.
I have some sales contact data for my imaginary business. Unfortunately, I have some complaints and I want to allocate them to the correct salesperson. I have the date of the complaint and the date the salesperson took over as the primary, as follows:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I start by extracting my data and importing it into Power Query. I have two tables. I click somewhere in my first table and I choose ‘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/221/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I accept the defaults (as usual!) and click OK.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
I ‘Close & Load’ my first table and repeat this process for the other table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I now have two tables, which I have called ‘Key Salesperson’ and Complaints.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I am going to merge my tables, so I choose ‘Merge Queries’, then ‘Merge Queries as New’, from the Combine section on the Home tab. I choose to create a new table because I am going to join my tables in another way next time…
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
In the join options, I can see that the dates are not going to help me join my tables: I need to use a full outer join.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
When I choose this, I can see there is more work to do to get the data into the format I require.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
I start by expanding the Complaints table, using the icon next to the column heading.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
I don’t need to use the prefix option as my columns have unique names.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
I create a new date column which will hold whichever date is populated.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I click OK to create Incident Date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image12.png/a1537847463e660a31158c8032525438.jpg)
I remove the other date fields and sort on Incident Date.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image13.png/917da985be13220165c8d2823e95344f.jpg)
I fill down the salesperson name by selecting Primary Salesperson, right-clicking and selecting Fill and then ‘Fill Down’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
Finally, I select out rows where Complaint Description is null.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
This gives me the data in the required format:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/221/image16.png/d082e3477129350b8a2a589156028e63.jpg)
Next time, I’ll look at a much quicker way to achieve this result…
Come back next time for more ways to use Power Query!