Power Query: Join or List - Part 4

Welcome to our Power Query blog.  This week, I continue comparing alternative approaches to extracting data from another table, this time with approximate matching.

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. 

Last time, I moved onto the second example, which required an approximate match.  To remind you, I have two more tables:

The plan is to encourage my salespeople to work harder, by linking their commission to each transaction.  Last time, 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!

I had no exact matches, just a lot of null values!

This time, I will transform this data to get to the desired result.  I start by creating a ‘Conditional Column’ from the ‘Add Column’ tab:

Note that I have selected a column for the ‘Output’ and ‘Else’ values, so that I can pick the value in Commissions.Amount if the value in Amount is null, otherwise I use the value in Amount.

This gives me a new column, and I set the data type to a decimal number:

Next, I ‘Sort Ascending’ on Commission Band.  I access this option from the filter arrow dropdown:

Next, I ‘Sort Ascending’ on Commission Band.  I access this option from the filter arrow dropdown:

I no longer need the Commissions.Commission Rate or the Commission Band columns so I select them both, and right-click to ‘Remove Columns’.

I select the filter arrow dropdown next to the Name column, and choose to ‘Remove Empty’:

Then, I can tidy up my data types and view the commission rates for each transaction.  I also rename   Commissions.Commission Rate to Commission.

To calculate the amount of commission, I go to the ‘Add Column’ tab and used the ‘Standard’ dropdown to ‘Multiply’ them:

This gives me a new column called Multiplication.  If I select this column and Amount, I can use another method to add a column using a standard function.  I can right-click and choose Sum:

This gives me a new column called Addition:

I can now remove the Amount,Commission and Multiplication columns (this time I select them and use the [Delete] key) and rename Addition to Amount.  I sort my data by Date, and rename the query Amounts Inclusive of Commission. 

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

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