JoinKind.RightAnti

Updated on

JoinKind.RightAnti (5) is an enumeration that specifies the kind of join operation. It is a member of the JoinKind.Type and represents a right anti join which returns all rows from the second table that do not have a match in the first table.

Imagine this – you’re joining the ‘Left Table’ and the ‘Right Table’ on a common column. The image below shows how a ‘right anti join’ works. In this case, you get a new table that includes only the elements from the ‘Right Table’ that do not have any matches in the ‘Left Table’. Any rows from the ‘Right Table’ that have matching rows in the ‘Left Table’ are excluded.

Right Anti Join in Power Query M

Examples

The right anti join returns all rows in Table2, not available in Table1. To specify the right anti join operation for the Table.NestedJoin function you can use its 6th argument.

= Table.NestedJoin(
      CustomersTable,
      {"CustomerID_1"},
      OrdersTable,
      {"CustomerID_2"},
      "Orders"           
      JoinKind.RightAnti // specifies the JoinKind
  )

/* ----------------------------------------
    You can also write the short-hand value
 ------------------------------------------ */

= Table.NestedJoin(
      CustomersTable,
      {"CustomerID_1"},
      OrdersTable,
      {"CustomerID_2"},
      "Orders"           
      5                   // the concise way to specify the right anti join
  )

Learn more about JoinKind.RightAnti in the following articles:

  • Join Types in Power Query
    Power Query has different join types for merging queries. Mastering them allows you to easily retrieve new values, keep relevant ones or discard them.. » Read more

Other related enumerations are:

Applies to

Here’s a list of functions that work with JoinKind.Type:

Contribute » | Contributors: Rick de Groot