JoinKind.LeftSemi

Updated on
Rick de Groot

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

Examples

So how can we use the JoinKind.LeftSemi enumeration? Suppose you have the following table called factOutage:

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

You want to filter this table down to only keep the rows that match with the dimStore table:

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

To do that, you can perform a merge starting in the factOutage table and comparing dimStore. Since the user interface does not (yet) support the JoinKind.LeftSemi enumeration, you can setup another join type, and adjust the code to include the left semi join. This is the code you can use: 

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

The result of this operation is a table that filters only matching rows (similar to an inner join). Here we only have rows for StoreKeys 2 and 4:

Left Semi Join filters only matching rows in Power Query M

Even though the merge operation creates a nested table object in a new column, expanding them has no use. A Semi Join can never return values from a joined table. Expanding them will return null values, as illustrated below:

Expanding a Left Semi Join results in null values in Power Query M

Keep in mind that you can use two notations for an enumeration. The following two are equivalent: 

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

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

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

Learn more about JoinKind.LeftSemi 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