Table.NestedJoin

Table.NestedJoin is a Power Query M function that joins the rows of two tables based on the equality of specified key columns and inserts the results into a new column. The function returns a table with the combined rows and allows for optional join kinds and key equality comparers.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.NestedJoin(
   table1 as table,
   key1 as any,
   table2 as any,
   key2 as any,
   newColumnName as text,
   optional joinKind as nullable number,
   optional keyEqualityComparers as nullable list,
) as table

Argument	Attribute	Description
Table1		This is the main table you’re starting with.
Key1		This is the column (or columns) in Table1 that you want to match with Table2. List them as text values like this: { “Col1”, “Col2″, “Col3” }.
Table2		This is the second table you’re joining with the main table.
Key2		These are the column (or columns) in Table2 that match with Key1 from Table1. If you have more than one column, they should be in the same order as in Key1. List them like this: { “Col4”, “Col5″, “Col6” }.
NewColumnName		This is the new column name that appears after the join. It will contain the matching records from Table2.
JoinKind	Optional	Join Types indicate how to join the tables. Options are JoinKind.LeftOuter, JoinKind.LeftAnti, JoinKind.Inner, JoinKind.FullOuter, JoinKind.RightOuter and JoinKind.RightAnti. Defaults to Left Outer Join if unspecified.
KeyEqualityComparers	Optional	You can provide a set of keyEqualityComparers to specify how to compare the key columns. However, argument is currently not documented and only meant for internal use.

Description

Joins the rows of table1 with the rows of table2 based on the equality of the values of the key columns selected by key1 (for table1) and key2 (for table2). The results are entered into the column named newColumnName.

The optional joinKind specifies the kind of join to perform. By default, a left outer join is performed if a joinKind is not specified.

An optional set of keyEqualityComparers may be included to specify how to compare the key columns. This feature is currently intended for internal use only.

Examples

You can join two tables using a single join column and perform a left outer join.

= Table.NestedJoin( 
    Table1,                 // Joins Table1
    { "CustomerID_T1" },    // Using single join column
    Table2,                 // With Table2
    { "CustomerID_T2" },    // On this join column
    "Orders"
 )

/* ---------------------------
    above code is identical to
 ----------------------------- */

= Table.NestedJoin( 
    Table1,
    { "CustomerID_T1" },
    Table2,
    { "CustomerID_T2" },
    "Orders",
    JoinKind.LeftOuter   // when unspecified, function uses a LeftOuter.Join
 )

This creates an additional column with the name Orders. You will find the matching records for each row in the added Table Object in this newly created column.

The Table.NestedJoin function also supports performing joins using multiple join columns.

= Table.NestedJoin( 
    Table1,
    { "Customer_T1", "Country_T1" }, // Provide one or more join columns from Table1
    Table2,
    { "Customer_T2", "Country_T2" }, // In a similar order, provide the join columns from Table2
    "Orders",
    JoinKind.Inner                   // Performs an inner join
 )

To see the entire operation, create a blank query and insert below code into the Advanced Editor.

let
    CustomersTable = // Creation of the first table, CustomersTable.
        Table.FromRecords(
            {
                [ Customer_1 = 1, Name = "John" ],
                [ Customer_1 = 2, Name = "Sara" ]
            },
            // Defines the data types for the columns in the CustomersTable.
            type table[Customer_1 = Int64.Type, Name = Text.Type ] 
        ),

    OrdersTable = // Creation of the second table, OdersTable.
        Table.FromRecords(
            {   
                [ Customer_2 = 1, Product = "Laptop",     Quantity = 2 ],
                [ Customer_2 = 2, Product = "Smartphone", Quantity = 1 ],
                [ Customer_2 = 3, Product = "Tablet",     Quantity = 5 ]
            },
            // Defines the data types for the columns in the OrdersTable.
            type table[ Customer_2 = Int64.Type, Product = Text.Type, Quantity = Int64.Type ] 
        ),

    // Left outer join CustomersTable and OrdersTable on the Customer_1 and Customer_2 columns.
    // This creates a "Orders" column in the CustomersTable, containing matching records.
    // When no records in the OrdersTable match, the "Orders" column will contain empty tables.

    LeftOuterJoin = 
        Table.NestedJoin( 
            CustomersTable,   
            {"Customer_1"}, 
            OrdersTable,     
            {"Customer_2"}, 
            "Orders",         
            JoinKind.LeftOuter
        ),
    // The nested "Orders" column expands to include "Product", and "Quantity" columns from OrdersTable.
    // If the "Orders" column was null these columns will return null values in the expanded columns.
    #"Expanded Orders" = 
        Table.ExpandTableColumn(
            LeftOuterJoin, 
            "Orders", 
            { "Product", "Quantity" }, 
            { "Product", "Quantity" } 
        )
in
    #"Expanded Orders"

Related articles

Learn more about Table.NestedJoin 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

Related functions

Other functions related to Table.NestedJoin are:

• Table.AddFuzzyClusterColumn
• Table.AddJoinColumn
• Table.FuzzyJoin
• Table.FuzzyNestedJoin
• Table.Join

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-nestedjoin