Power Query: Join or List – Part 1

Welcome to our Power Query blog.  This week, I begin a series of blogs comparing alternative approaches to extracting data from another table.

I know you’ve 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.  The first example, and the simplest, is exact matching.  Later in the series I will also look at approximate matching.

I have 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. 

I extract both sets of data into Power Query by using the ‘From Table/Range’ option in the ‘Get & Transform’ section of the Data tab, one set at a time:

To begin with, I choose to ‘Close & Load To’ from the Home tab in the Power Query editor to trigger the ‘Import Data’ dialog:

I choose to ‘Only Create Connection’, and load the other set of data:

This gives me two queries, which I rename to Expenses and Permissions.

The first method I am going to use is to merge the tables to find out who has expenses that are not allowed or that require more information.  I start in Expenses, and I choose ‘Merge Queries’ from the Home tab:

This enables the ‘Merge Queries’ dialog:

I match the data (using a ‘Left Outer’ join) and I click OK.

I use the icon in the Permissions column to extract the data from the tables:

I only need the information in Column2.

I have the data I need, and I rename the column Allowed to Claim?

Next time, I will use List functions to achieve the same result.

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