JoinKind.LeftOuter

Updated on

JoinKind.LeftOuter (1) is an enumeration that specifies the kind of join operation. It is a member of the JoinKind.Type and represents a left outer join which ensures that all rows of the first table appear in the result.

Imagine this – you’re joining the ‘Left Table’ and the ‘Right Table’ on a common column. The image below shows how a ‘left outer join’ works. In this case, you get a new table that includes all elements from the ‘Left Table’ and only the matching elements from the ‘Right Table’. If there’s no match for a row from the ‘Left Table’ in the ‘Right Table’, the columns from the ‘Right Table’ will simply show empty in the resulting table.

Left Outer Join in Power Query M

Examples

The left outer join returns all rows in Table1, and adds matching records from Table2. To specify the left outer join operation for the Table.NestedJoin function you can use its 6th argument.

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

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

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

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

BI Gorilla Youtube Channel

Contribute » | Contributors: Rick de Groot