Locked lesson.

Upgrade

About this lesson

Some of the most powerful joins for those trying to perform reconciliations are those that identify the items that do NOT match. In this case we will explore how the Left Anti, Right Anti and Full Anti joins can be used to accomplish this.

Exercise files

Download this lesson’s related exercise files. You can download source data files for the course from the resources section of your Lessons page.

Anti Joins.xlsx
25.6 KB Anti Joins - Completed.xlsx
30.6 KB

Quick reference

Anti Joins

A demonstration of merging using Anti Joins.

When to use

When you want to compare two tables and identify which records in the first table do not have a match in the second table.

Instructions

Create staging queries for the source data tables

For each data source

  • Create a new query that points to the data source
  • Perform whatever transformations are required to clean up the data
  • Finalize the query by choosing Home --> Close & Load To… --> Only Create Connection

Merge the staging queries

  • Open the Queries & Connections pane in Excel
  • Right click one of the tables you wish to merge --> Merge
  • Choose the other table you wish to merge
  • Select the column to use as the merge key (i.e. the one with values that are the same in both tables so that Power Query knows which records to match together) in both tables
  • Choose the Join Kind and click OK
  • Rename the newly created query
  • Expand the new column(s) and choose which data to include
  • Define the data types for the columns
  • Finalize the query by loading it to the desired destination

Creating a Full Anti Join

  • Create merged table with a Left Anti Join
  • Create a merged table with a Right Anti Join
  • Append the two tables together

Hints & tips

  • A merge key that is based on multiple columns is called a concatenated key
    • The order in which you select the columns for creating the merge key is important, so make sure you select them in the same order for both columns
  • For non-subscription versions of Excel 2016, there is no option to select the join type when merging as only Left Outer Joins are available
    • To create the other join types, first build a merge with a Left Outer Join and then manually change the merge type in the formula bar
Login to download
  • 00:04 What I'd like to do here is I would like to actually compare these two lists,
  • 00:08 but I've got a challenge.
  • 00:11 What I'm interested in here is only the items that don't match.
  • 00:14 I wanna see the red and
  • 00:15 the yellow items because those ones are ones that I need to track down.
  • 00:19 Every time there's a record in blue or white,
  • 00:22 it means that the account department combination that's on the left-hand side
  • 00:26 matches to what's on the right-hand table.
  • 00:28 So those are totally okay but the red records don't match and
  • 00:32 the yellow records don't match the other table either.
  • 00:34 I need a list to focus just on those with no extra noise from the system.
  • 00:39 You'll notice that my Queries in Connections pane is already open here.
  • 00:42 And I've got my transaction query already setup as a pointer.
  • 00:46 And my chart of accounts query is also setup as another staging query that points
  • 00:50 back to the original tables.
  • 00:51 So, I'm already to go on to my merges.
  • 00:54 I'm gonna right click on transactions and I'm gonna say, merge.
  • 01:01 Once I get here, I'll pick the Chart of Accounts table.
  • 01:05 And I'm going to hold down my Ctrl key for Account and Department and hold down my
  • 01:09 Ctrl key again to build a concatenated key for Account and Department.
  • 01:13 And at this point, I'm gonna change from a Left Outer to a Left Anti-Join.
  • 01:20 Now, if you're in the non subscription version of Excel 2016,
  • 01:24 you don't have this option.
  • 01:26 But what you can do, is you can just use your left outer join to start with and
  • 01:30 then say OK.
  • 01:34 When you get inside here, if you're in Excel 2016,
  • 01:37 you need to change this piece here where it says LeftOuter to LeftAnti.
  • 01:42 Just make sure that you capitalize the L and the A and at that point,
  • 01:46 you'll be looking at the same thing that we are.
  • 01:49 Just the two records on the left hand side and
  • 01:52 if you click in the table, it'll actually show you, null, null, null.
  • 01:56 And null, null, null.
  • 01:58 The reason why this is going on is because it's pulling off the read records from
  • 02:03 the left hand table.
  • 02:04 And it says okay, well that's fine, there's no matches on the right.
  • 02:07 So it's feeding it with nulls.
  • 02:09 Now you might look at that and go well, there's no value in this data, so
  • 02:12 I could just right click and delete these.
  • 02:14 And that's true, you could.
  • 02:15 If this was all you were interested in was the Left Anti,
  • 02:19 you could absolutely delete the COA column now and you're done.
  • 02:22 It's all good.
  • 02:23 But we wanna go a little bit bigger than this.
  • 02:26 We're gonna expand this and I'm gonna use the original column name as prefix and
  • 02:31 we'll leave these three guys set.
  • 02:33 Say OK, and it expands it out to show the null values.
  • 02:38 I'm now gonna go and give this query a name and I'm gonna call it Left Anti.
  • 02:46 I'm also gonna go and load this as a connection only query.
  • 02:50 So let's say Close and Load to and we'll choose to Only Create Connection.
  • 02:58 I'm now gonna do the same for the Right Anti, so right-click, we're gonna merge.
  • 03:06 We're gonna choose to merge to the chart of accounts and
  • 03:10 we'll set up that nice consolidated key here for account and department.
  • 03:15 By the way, the order I've selected these is important.
  • 03:18 If I were to go the other way around, the department would be one and
  • 03:20 account would be two, so you wanna make sure that's the same direction.
  • 03:24 I'm then gonna change this, again, to a Right Anti.
  • 03:27 And again, if you're in the non-subscription Excel 2016,
  • 03:30 you would go an just use a left outer.
  • 03:33 Notice that it's only matched six out of the first eight rows that
  • 03:36 tells me that there is obviously a mismatch between these things.
  • 03:39 Or at least, there likely is a mismatch, depending on the size of the previews.
  • 03:43 We'll now say OK.
  • 03:47 Once again, when we're in here,
  • 03:49 you'll notice we have entirely nulls all the way across.
  • 03:53 If I click in the preview on the table here, it shows me the data.
  • 03:56 If I only care about the Right Anti portion, just this guy down the bottom in
  • 04:01 the preview, then I could just right click on the chart of accounts column and
  • 04:05 I can say remove other columns.
  • 04:07 But again, we wanna do something bigger here and
  • 04:09 I need consistent column names between my tables.
  • 04:12 So I'm gonna go and expand this and we'll use the original column name's prefix and
  • 04:16 we'll say OK and it expands everything quite nicely for me.
  • 04:21 I'm now gonna go and say cool, let's call this one Right Anti.
  • 04:26 And again, I'm going to load this close in load two to connection only.
  • 04:36 So now, I have my Left Anti-Join, and I have my Right Anti-Join.
  • 04:40 So these are the two components that actually make up the entire thing of all
  • 04:44 of the mismatched records.
  • 04:46 So at this point, what I'm gonna do is I'm gonna build myself a Full Anti.
  • 04:50 I'm gonna right click on Left Anti.
  • 04:51 I'm gonna say Append.
  • 04:55 And when it gives me the option, I'm gonna come in here and
  • 04:57 say give me the Right Anti-Join.
  • 05:00 Because these have exactly the same column headers, it will now stack them.
  • 05:06 And this is why we actually preserved the column headers and we expanded them,
  • 05:10 so everything is the same.
  • 05:12 Now what I can see is the null, null, nulls here for
  • 05:14 the guys who were from the left hand side.
  • 05:16 I can see the null, null, nulls for the guys who were on the right hand side, and
  • 05:20 everything looks good.
  • 05:21 And I can go and I'll call this one Full Anti.
  • 05:25 And from here, all I need to do is say close and load.
  • 05:30 I'm just gonna go with the close and load default because I want to load
  • 05:33 my exceptions list out to the worksheet and there it is.
  • 05:37 So this is great because now every time the data in those
  • 05:40 original tables is refreshed, I can refresh this and
  • 05:43 it will give me a beautiful listing to tell me not only if there
  • 05:47 is any exceptions but exactly which ones they are and don't match.

Lesson notes are only available for subscribers.

Gift this course