Table.Join

Table.Join is a Power Query M function that combines the rows of two tables based on the equality of specified key columns. The function returns a new table with the rows from both tables, and allows for different types of joins (inner, left outer, right outer, full outer, left anti, and right anti).

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

Syntax

Table.Join(
   table1 as table,
   key1 as any,
   table2 as table,
   key2 as any,
   optional joinKind as nullable number,
   optional joinAlgorithm as nullable number,
   optional keyEqualityComparers as nullable list,
) as table

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).

By default, an inner join is performed, however an optional joinKind may be included to specify the type of join. Options include:

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

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

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

= Table.Join( 
    Table1,
    { "CustomerID_T1" },
    Table2,
    { "CustomerID_T2" },
    JoinKind.Inner         // when unspecified, function uses an Inner.Join
 )
Table.Join with single join column in Power Query M

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

Table.Join( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    "CustomerID",
    Table.FromRecords( {
        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],
        [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],
        [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],
        [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],
        [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],
        [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25]
    } ),
    "CustomerID"
 )

However, notice that this setup only works for the Inner.Join. When you try to apply this same logic for one of the other JoinTypes, Power Query returns an error:

An error occurred in the ‘’ query. Expression.Error: A join operation cannot result in a table with duplicate column names (“CustomerID”).
Details: [Type]

In those cases you will either need to perform the action using Table.NestedJoin or make sure there are no duplicate column names.

Other functions related to Table.Join are:

BI Gorilla Blog

Last update: August 28, 2023 | Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-join
© 2023 BI Gorilla. All rights reserved. Content derived from Microsoft documentation is property of Microsoft Corp.