Last week’s blog post on merging tables using Outer joins has proved to be pretty popular.  (I guess I’m not the only one who struggled with this concept!) This week we’ll look at the remaining three options, showing how to merge tables using Inner and Anti joins.

The Inner and Anti join types:

Again, we have three join types to explore this week:

• Inner Join
• Left Anti Join
• Right Anti Join

If you read last week’s article you may already have an idea of what you’ll be seeing here, but we’ll make sure we go through it in full anyway.  (If you HAVEN’T read last week’s article, you might want to do so, as this one just builds on steps that readers will already be comfortable with.)

Sample Data

We’re going to work with the same set of data as we did last week, although we have a different sample file for it (to hold the completed queries.)  That file can be downloaded here.

As a reminder, here’s what we’re set up with:

As I explained last week:

… two tables of data, one called Transactions, and one called ChartOfAccounts.

Now, the key piece you want to watch here is that we need the Account-Dept combination to exist in both tables in order to make a perfect join between them.  Think VLOOKUP for a second… if you tried to make a VLOOKUP for account 10045 from the Transactions table against the ChartOfAccounts table, what would you get?  Of course, you’d get #N/A since 10045 doesn’t exist in the ChartOfAccounts table.

In this case we have items in both tables that don’t exist in the other.  (The yellow records in the Transactions table don’t have a match in the ChartOfAccounts table, and the red records in ChartOfAccounts don’t have a match in the Transactions table.)  With these differences we can test how each of the first three join types available to us behave when we try to merge the data in both tables together.

We’ve already got the ChartOfAccounts query and Transactions queries set up as connection only queries, so we’re ready to jump right into comparing the join types.

Merge Tables using Inner and Anti Joins – Inner Join

This join type stands out somewhat from the others in that there is no “left” or “right” version.  Let’s build the join to explore why that is:

• Open the Workbook Queries pane
• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to “Inner (only matching rows)”
• Click OK

Like last week, the data lands in Power Query, and we’ll take the following steps to expand the rows:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

The results of these steps are shown here:

We can now see the meaning of the Inner Join.  Unlike the Full Outer Join that pulls in all records from both tables whether there is a match or not, the Inner Join only pulls in rows that exist in both the left and right tables.  In other words… all those red and yellow rows shown in our original data set?  They’re missing from this output.

I can see this being very useful for indentifying matching records, giving a list to show which ones matched without polluting the data set with non-matching items.

Let’s finalize this query and move to the Anti Joins:

• Change the name of the query to Inner
• Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

Merge Tables using Inner and Anti Joins – Left Anti Join

Now that we’ve seen Outer and Inner joins, give some thought as to what an Anti join might do…

Done?  Let’s go see if you’re right.

• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to “Left Anti (rows only in first)”
• Click OK

And when the data gets to Power Query:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

You should now have this:

So this time, we only see the records from the left table (Transactions) that had no matching record in the ChartOfAccounts (right) table.  How cool is that?  This allows us to immediately identify records with no matches at all.

Let’s finalize this query as well:

• Change the name of the query to Left Anti
• Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

Merge Tables using Inner and Anti Joins – Right Anti Join

By this point I’m sure you can predict where this is going.  So let’s get to it:

• Right click the Transactions query and choose Merge
• Select ChartOfAccounts for the bottom table
• For the top query (Transactions) select Account, hold down CTRL and select Dept
• For the bottom query (ChartOfAccounts) select Account, hold down CTRL and select Dept
• Change the Join type to “Right Anti (rows only in second)”
• Click OK

And when the data gets to Power Query:

• Right click the NewColumn column –> Rename –> COA
• Click the Expand icon on the top right of the COA column
• Leave all the defaults and click OK

And, in anti-climactic fashion (ha!) we end up with the following:

Yes indeed, as you probably predicted, only the records shown highlighted in yellow in the ChartOfAccounts (right) table show up in this set.  No record that has a match in the Transactions (left) table, nor any records in the Transactions table without a match in the ChartOfAccounts table show up.

We can now finish our final comparison:

• Change the name of the query to Right Anti
• Go to Home –> Close & Load –> Close & Load To… –> Only Create Connection

Final Thoughts

I think this is pretty cool stuff.  If you’d asked me how many ways there are to join data, I think I would have been hard pressed to answer six until I wrote this up.  But seeing it laid out in full, with each option detailed… I can see where these would each be useful in their own right.  I hope you enjoyed taking the journey with me here!

Oh… and by the way… if you’d like to download one workbook with all six join types included… you can do that here.