Power Query: Join or List - Part 6

Welcome to our Power Query blog.  This week, I continue to continue comparing alternative approaches to extracting data from another table with approximate matching using List() functions.

I know you have missed them: my imaginary salespeople are back!  They are going to help me compare alternative approaches to pulling in data from one query to another, namely merging and using list functions.  There are two examples that I am going to use in this series.  I have covered the first example, which used exact matching. 

In Part 1 and Part 2, I looked at these two tables.  The first is a list of item types that my salespeople have been putting under ‘personal’ on expenses.  The second is a list indicating which are allowed and which are not, and any that require further information. 

In Part 1, I merged the queries to get the result:

In Part 2, I used List functions to achieve the same result. 

In Part 3, I moved onto the second example, which requires an approximate match.  I have two more tables:

The plan is to encourage my salespeople to work harder, by linking their commission to each transaction.  I extracted my data to Power Query and set about merging my queries:

Having transformed the resulting data, it seemed like I had a long way to go!

In Part 4, I transformed this data to get to the desired result.  

Last time, I created a reference of Sales, which I called Sales (list).

I added a new column Commission Rate to Sales (list), which comprised of a list of all the Amountvalues from Commissions which were less than or equal to the Amounton Sales (list). 

There are a couple of ways to get to the correct Commission Rate on Commissions.  I could start by using List.Max():

This simply finds the maximum value in a list.  If the list is empty, it will return null.   

This simply finds the maximum value in a list.  If the list is empty, it will return null.   

This gives me my results, but for those rows with nulls, I get an error, this is because the list is empty:

I could fix this by having a zero [0] rate band on the Commissions table, but assuming I don’t have any control over the input data, I can just replace errors with zero [0]:

This solves the problem:

From here, the process is the same as I did for Part 4:

The results are the same.  Next time, I’ll take a look at some alternative List() functionality that I could have used.

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