JoinKind.FullOuter

Updated on

JoinKind.FullOuter (3) is an enumeration that specifies the kind of join operation. It is a member of the JoinKind.Type and represents a full outer join which ensures that all rows of both tables appear in the result, with rows that did not have a match in the other table joined with a default row containing null values for all of its columns.

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

Full outer Join in Power Query M

Examples

The full outer join returns all rows of Table1 and Table2. To specify the full outer join operation for the Table.NestedJoin function you can use its 6th argument.

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

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

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

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