Power Query: Emerging from a Muddle
3 March 2021
Welcome to our Power Query blog. This week, I look at a more efficient way of merging the data from last week, again when there is not a clear link between the tables,
I begin by repeating the scenario from last time. 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 also the date the salesperson took over as the primary.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
I start by extracting my data to 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/222/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I take the usual defaults and click OK.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/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/222/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
I now have two tables, which I have called ‘Key Salesperson’ and Complaints. At this point last week, I used a full outer join to connect my tables. This week, I am going to join them conditionally. I will write a function to get the correct salesperson for each complaint.
I start with my ‘Key Salesperson’ table. Considering this table from the perspective of the Complaints table, I will need to know which salesperson was the primary contact for a given Complaint Date, so I start by generating some of the M code I will need. I will need to find the salesperson who takes over before the date of the complaint.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
I filter on the date, and for now, I just choose to filter before one of the dates already in the table. It doesn’t matter what date I use at this point; I just need the syntax. My next consideration is that this could return more than one salesperson, and I want to get just the latest one.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
I need to sort the data in reverse date order, to give me the most recent record at the top.
Finally, I drill down into the top value to ensure that my function will return the name of the salesperson.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
This gives me a single value:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
Now I have the code I need; I am going to turn this into a function.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
The code I have in the advanced editor so far is:
let
Source = #"Key Salesperson",
#"Filtered Rows" = Table.SelectRows(Source, each [Date] < #datetime(2020, 1, 15, 0, 0, 0)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}}),
#"Primary Sales Person" = #"Sorted Rows"{0}[Primary Sales Person]
in
#"Primary Sales Person"
I will pass a parameter p_complaintdate, which I will use instead of the hardcoded date in the #Filtered Rows step.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
The M code is now:
(p_complaintdate as date) =>
let
Source = #"Key Salesperson",
#"Filtered Rows" = Table.SelectRows(Source, each [Date] < p_complaintdate),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Descending}}),
#"Primary Sales Person" = #"Sorted Rows"{0}[Primary Sales Person]
in
#"Primary Sales Person"
I can then save this as a function fn_salesperson and go back to my Complaints table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I am going to add a column from the ‘Add Column’ tab to ‘Invoke Custom Function’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image12.png/a1537847463e660a31158c8032525438.jpg)
I choose my function and select to use Complaint Date as the parameter.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2021/power-query/222/image13.png/917da985be13220165c8d2823e95344f.jpg)
This gives me the correct salesperson for each complaint.
Come back next time for more ways to use Power Query!