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

Argument	Attribute	Description
table1		The base table for the join.
key1		One or multiple keys to perform the join on. For a single key, you can provide a text value, whereas for multiple you can specify the column names within a list.
table2		The second table involved in the join.
key2		One or multiple keys to perform the join on. For a single key, you can provide a text value, whereas for multiple you can specify the column names within a list.
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 an Inner Join if unspecified.
joinAlgorithm	optional	Specifies the method used to join two tables. By default, JoinAlgorithm.Dynamic is used, however, you can specify an optional JoinAlgoritm.Type. Options include: – JoinAlgorithm.PairwiseHash: Buffers both tables until one is fully buffered, then executes a LeftHash or RightHash join. – JoinAlgorithm.SortMerge: Executes a streaming merge, assuming both tables are sorted by join keys. – JoinAlgorithm.LeftHash: Buffers the left table for a lookup and streams the right table, joining each right row with corresponding left rows. – JoinAlgorithm.RightHash: Buffers the right table for a lookup and streams the left table, joining each left row with corresponding right rows. – JoinAlgorithm.LeftIndex: Utilizes left table keys to query the right table in batches. – JoinAlgorithm.RightIndex: Uses right table keys to query the left table in batches.
keyEqualityComparers	optional	Specifies how to compare the key columns. This functionality is currently intend for internal use only.

Description

The Table.Join function merges two tables by matching rows based on specified key columns. It allows for various join types, depending on whether and how unmatched rows should be included in the output. The function can also employ different join algorithms, to optimize the process based on table sizes and characteristics.

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
 )

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.

Related functions

Other functions related to Table.Join are:

• Table.AddFuzzyClusterColumn
• Table.AddJoinColumn
• Table.FuzzyJoin
• Table.FuzzyNestedJoin
• Table.NestedJoin

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