JoinKind.RightSemi

Updated on
Rick de Groot

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

Examples

Let’s see how the Right Semi Join works in practice. Suppose you’re working with the below factOutage Table:

Table for Left Semi Join and Right Semi Join in Power Query M

You want to find out if the dataset contains any stores from the below dimStore table:

Customer Dimension for Left Semi Join and Right Semi Join in Power Query M

Starting from the Outage table you use the merge queries option to compare rows based on the StoreKey column. You adjust the joinkind enumeration to make use of JoinKind.RightSemi. Here’s the code you can use: 

Table.NestedJoin( 
  Source, 
  {"StoreKey"}, 
  DimStore, 
  {"StoreKey"}, 
  "DimStore.1", 
  JoinKind.RightSemi 
)

Here’s the resulting table:

Right Semi Join returns matching rows from right table in Power Query M

As you can see, the original factOutage table does not contain any values. Yet, now you can find a nested table object containing the matching rows from the dimStore table. That’s exactly what the enumeration does. Return only values in the right table, that match in the left.

The following two expressions are equivalent, where one uses the JoinKind.RightSemi enumeration and the other uses the shorthand form:

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

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

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

Learn more about JoinKind.RightSemi in the following articles:

Other related enumerations are:

Applies to

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

Contribute » | Contributors: Rick de Groot

2023-2024 © BI Gorilla. All rights are reserved. Information from Microsoft docs is property of Microsoft Corp. | Privacy Policy