Power Query: If You Can’t Tell Them Apart, Join Them

Welcome to our Power Query blog. This week I look at how to merge queries to compare two similar lists.

Sometimes, the tasks I have to do are less than thrilling. I could be given two long lists of employees, and told to find who is missing from each list:

With Power Query, there is an easy way to do this.

I have two lists of employees which look virtually identical, but of course they are not. The first step I take is to use Power Query to load my lists. I begin by creating my first query.

On the ‘POWER QUERY’ tab, I use the ‘From Table / Range’ option in the ‘Excel Data’ section. I can either convert my data into a table before I use this option or let Power Query do this as part of my query creation.

My data appears in a new query, which I call ‘Employee Table 1’. I choose the ‘Close and Load to’ option on the ‘Close and Load’ section on the ‘Home’ tab.

I only want to create the connection as I am going to use my queries for comparison. Having created the first query, I repeat the process for the second employee list.

My next step is to merge my queries and to do this I can right-click on one of my employee queries (I could have chosen to use the option from the ‘Combine’ section of the ‘POWER QUERY’ tab).

I select the ‘Merge’ option.

In the ‘Merge’ screen, I specify the second employee table, and I see a preview of the data in both queries. I want to merge my tables completely, so I select all columns in both tables.

At the bottom of the screen I have the option to specify the ‘Join Kind’, and this will determine what data I see when the queries are merged.

I am interested in the joins that will either find my matching or missing data, which are the last three on the drop down. I will run through each option in turn.

‘Inner’

This will give me those employees that are on both lists. I pick this option first.

The rows I can see initially are those rows which match the criteria in the first table, and I have an extra column which is labelled ‘Employee Table 2’, which contains a table. I choose to expand the second table to see which rows match the criteria in the second table:

I don’t need to use any original column names as a prefix, so I uncheck that box and choose ‘OK’.

I have 42 employees in both tables.

‘Left Anti’

I opt to merge my tables again, and this time I pick join type ‘Left Anti’.

This time I am interested in the first table, since this will show me those employees in ‘Employee Table 1’ who are missing from ‘Employee Table 2’. I find four employees and two seem a little familiar… Notice Zoe, as she’ll come up again later…

Just to show that the second table will be empty (as I am looking for employees that don’t appear here), I expand the table viz.

‘Right Anti’

My final choice is to create a merge with a ‘Right Anti’ join. In the Query Editor, this looks a little strange before I expand the table.

This is because I am seeing the results from the first table, and since I am looking for values that are in the second table and not in the first table, it is empty. I choose to expand the second table:

I have two employees that are missing from the first table – and it wouldn’t take too much investigation to discover that Zoe has changed her name from Green to Brown!

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.

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