Left anti join

  • Article

One of the join kinds available in the Merge dialog box in Power Query is a left anti join, which brings in only rows from the left table that don't have any matching rows from the right table. More information: Merge operations overview

Left anti join example.

This article uses sample data to show how to do a merge operation with the left anti join. The sample source tables for this example are:

  • Sales: This table includes the fields Date, CountryID, and Units. CountryID is a whole number value that represents the unique identifier from the Countries table.

    Screenshot of the sales table containing Date, CountryID, and Units columns, with CountryID set to 1 in rows 1 and 2, 3 in row 3, and 2 in row 4.

  • Countries: This table is a reference table with the fields id and Country. The id field represents the unique identifier for each record.

    Screenshot of the countries table with ID set to 3 in row 1 and 4 in row 2 and Country set to Panama in row 1 and Spain in row 2.

In this example, you merge both tables, with the Sales table as the left table and the Countries table as the right one. The join is made between the following columns.

Field from the Sales table Field from the Countries table
CountryID ID

The goal is to create a table like the following, where only the rows from the left table that don't match any from the right table are kept.

Left anti join final table.

To do a left anti join:

  1. Select the Sales query, and then select Merge queries.
  2. In the Merge dialog box, under Right table for merge, select Countries.
  3. In the Sales table, select the CountryID column.
  4. In the Countries table, select the ID column.
  5. In the Join kind section, select Left anti.
  6. Select OK.

Screenshot of the Merge dialog showing the results of following the previous left anti join procedure.

Tip

Take a closer look at the message at the bottom of the dialog box that reads "The selection selects 3 of 4 rows from the first table." This message is crucial to understanding the result that you get from this operation.

In the Sales table, you have two instances of a CountryID of 1 and one instance of a CountryID of 2, but neither of them are found in the Countries table. Therefore, the match found three of four rows in the left (first) table.

In the Countries table, you have the Country Panama with an ID of 3, so that record isn't brought into the Sales table because the numbers match. In addition, you have a Country Spain with an ID of 4, but there are no records for CountryID 4 in the Sales table. Therefore, neither of the two rows from the right (second) table are brought into the Sales table.

From the newly created Countries column, expand the Country field. Don't select the Use original column name as prefix check box.

Screenshot of the Expand dialog with only the Country column selected.

After doing this operation, you created a table that looks like the following image. The newly expanded Country field doesn't have any values because the left anti join doesn't bring any values from the right table—it only keeps rows from the left table.

Final table for left anti join.