Power Query: Join or List - Part 5

Welcome to our Power Query blog.  This week, I 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!

Last time, I transformed this data to get to the desired result.  

This time, I’ll look at how I can achieve this with List() functions.

I start with the Sales and Commissions tables.  Note that for this method, it’s important that Commissions table is sorted in order of ascending Amount. 

Since I am going to use the Sales table and transform it to get my result, I’ll take a reference copy so that I don’t impact the merged query Amounts Inclusive of Commission that I created last time:

I call the new query Sales (list).

I am now going to add a new ‘Custom Column’ to Sales (list) from the ‘Add Column’ tab.  I will explain the M code used in a moment.

The M code is:

Let’s start by looking at how List.Select() works:

                        List.Select(list as list, selection as function) as list

This returns a list of values from the list, which match the selection condition.

If I consider the List.Select() portion of the M code, I have:

This is creating a list of all the values in the Amount column in Commissions, where each Amount value is  less than or equal to amt.  If I then look at the beginning of the M code, I am setting my variable amt to the current Amount in Sales (list).

This gives me a column of lists:

Clicking in the space next to the word ‘List’ allows me to see the contents:

Depending upon the value in Amount, there may be several or no items in the list: it depends which bonus threshold has been reached.  I need to extract the correct Commission Rate value using the column of lists.  I’ll look at how I can proceed next time.

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