Merge Operations in Power BI / Power Query – Part 3: Left Anti Join

IMPORTANT: You can read the official documentation that Microsoft has on this topic from the following link (url).

If you haven’t read Part 1 and Part 2 of this series, I highly recommend that you read those prior to this post.

In those previous posts, we went over the Left Outer Join and Right Outer Joins. At this point we got the basics on how Joins / Merge operations work inside Power Query / Power BI. It’s time to get even more clever with the usage of Merge Operations.

In this, Part 3, we’ll go over the Left Anti Join from a purely practical standpoint.

Source Data

Similar to the previous posts, we’ll be using 2 tables this time:

• ExportedData – similar to the previous post, you can imagine that this table comes out hot out of the Global system that the company uses and it comes with some data that I truly don’t need

• ExclusionTable – with the combination of values that we want to filter out of the ExportedData table

The Goal

In the previous post we were defining which combination of values we wanted to keep, but in this case we’re creating basically a table of Exclusions or values that we want to filter out of the original ExportedData table.

The result that we’re looking for should look like this:

The Solution

The following is a step by step process on how we can accomplish our desired result with nothing but a few clicks. No code needed – just the use of the Left Anti Join inside of Power Query / Power BI.

Loading the Data

You can follow along by downloading the sample file from the button below:

The first thing that we need to do is load both of those tables to Power Query / Power BI. To do that, we simply go to Data > Get & Transform Data > Get Data > From File > From Workbook:

then we find the file that we downloaded (with the name of Sample Data.xlsx). We then select the option to “Select multiple items” and we select both the ExportedData and ExclusionTable tables:

and then we click on ‘Transform Data’ or ‘Edit’ depending on what version of Power Query / Power BI you might have.

That should land both of your tables inside the Power Query editor as shown below:

Doing the Left Anti Join

At this stage, we’re ready to click on the ‘Merge Queries’ button that you see in the top middle of the previous image. Be sure to select, from the dropdown, the option to merge queries as new.

Now we get a new Window to set the Merge operation and in here we select our first table to be the ExportedData table and the second table should be the ExclusionTable.

Similar to what we did in Part 2 of this series, we are not only doing a merge based on 1 column, but actually on multiple as shown below:

Don’t forget to select the Left Anti from the Join Kind dropdown!

After hitting OK, you’ll go back to the Power Query Editor and you should be able to see it like this:

What happened? you might ask. In short, we got rid of the row where the Region was Asia and the Category Code was 2 by doing that simple Merge operation.

You can change the values from the original ExclusionTable and play with those values to simply get rid of the values that you need. You can even add more combinations!

For example, let’s say that I change the values from the ExclusionTable to be like the following:

then the final query / output table will look like this:

Final thoughts

This is what I like to call a Clever filter, but more specifically an Exclusion Table where you can define what rows NOT to load.

For my case, I truly don’t need to do anything with the newly created column, so I simply delete that column after doing the Merge operation. Here’s how my query looks like after deleting that column and, again, the important part here is the Join Operation and how it gives me the new subset of my original table: