Power Query: Join or List – Part 3

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:

Last time, I used List functions to achieve the same result. 

This time, I’ll move onto the second example, which requires an approximate match.  I have two more tables, viz.

The plan is to encourage my salespeople to work harder by linking their commission to each transaction.  I start by extracting the data from each table to Power Query, using ‘From Table/Range’ in the ‘Get & Transform’ section of the Data tab, which converts them to Excel Tables.

Having done this for both sets of data, I can view my queries in the Power Query editor.  I have named my new queries Sales and Commissions respectively.

Starting in Sales, on the Home tab, I choose to ‘Merge Queries as New’ from the ‘Merge Queries’ option:

I choose to merge Sales and Commissions:

Clearly, just linking on Amount and taking the defaults isn’t going to give me any results.  I need to change the join type to ‘Full Outer’.  Trust me, this will lead to the correct result in the end!

I click OK to get a new query, which is a reference copy of Sales, plus a new column called Commissions:

I can expand Commissions by clicking on the double headed arrow icon:

Since I have two columns called Amount, I will leave the ‘Use original column name as prefix’ checked and click OK.  The results don’t look too promising:

Since I have no exact matches, I have a lot of null values.  Next time, I will transform this data to get to the desired result…

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