Table.Join Function in Power Query M (4 Easy Examples)

Table.Join combines two tables into one by matching rows on a key column from each table.

If you want to pull related rows from a second table into your first one, matched on a shared ID, this is the function that does it.

Table of Contents

Syntax of Table.Join Function

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

where

table1 (required, table). The first table, often called the left table.
key1 (required, any). The column name in table1 to match on. Pass it as text, or a list of column names for a multi-column key.
table2 (required, table). The second table, often called the right table.
key2 (required, any). The column name in table2 to match on.
joinKind (optional, nullable number). Which rows to keep. One of JoinKind.Inner, JoinKind.LeftOuter, JoinKind.RightOuter, JoinKind.FullOuter, JoinKind.LeftAnti, or JoinKind.RightAnti. Defaults to JoinKind.Inner when omitted.
joinAlgorithm (optional, nullable number). The internal join strategy. Leave it off and Power Query picks one for you.
keyEqualityComparers (optional, nullable list). A list of comparers that control how keys are matched.

Returns: a single table whose columns are the combined columns of both input tables. The joinKind decides which rows survive the match.

In plain terms, you hand it two tables and the key column in each, and it lines up matching rows side by side.

Example 1: Inner join two tables on a shared key

Say you have a Trainers table and a Sessions table, and you want each session lined up with the trainer running it.

Both tables call the key column TrainerID.

Here is the starting data:

TrainerID	TrainerName
T-A	Maya
T-B	Liam
T-C	Noor

SessionID	TrainerID	Topic
101	T-A	Pivot Tables
102	T-A	Power Query
103	T-B	Charts
104	T-D	Macros

Join them on TrainerID, then sort so the row order is stable:

let
    Trainers = Excel.CurrentWorkbook(){[Name="Trainers"]}[Content],
    Sessions = Excel.CurrentWorkbook(){[Name="Sessions"]}[Content],
    Joined = Table.Join(Trainers,"TrainerID",Sessions,"TrainerID"),
    Result = Table.Sort(Joined,{{"SessionID",Order.Ascending}})
in
    Result

With no joinKind, this is an inner join, so only rows that match in both tables survive.

The result keeps the matched rows:

TrainerID	TrainerName	SessionID	Topic
T-A	Maya	101	Pivot Tables
T-A	Maya	102	Power Query
T-B	Liam	103	Charts

Noor has no session and session 104 (T-D) has no trainer, so both drop out. Because both keys share the name TrainerID, the inner join merges them into a single column.

Example 2: Keep unmatched rows with LeftOuter

This time you want every trainer in the result, even the ones with no booking yet.

The Bookings table names its key column Coach, not TrainerID.

Here is the starting data:

TrainerID	TrainerName
T-A	Maya
T-B	Liam
T-C	Noor

BookingID	Coach	Topic
B1	T-A	Pivot Tables
B2	T-B	Charts

Join with JoinKind.LeftOuter and drop the redundant Coach key for a clean result:

let
    Trainers = Excel.CurrentWorkbook(){[Name="Trainers2"]}[Content],
    Bookings = Excel.CurrentWorkbook(){[Name="Bookings2"]}[Content],
    Joined = Table.Join(Trainers,"TrainerID",Bookings,"Coach",JoinKind.LeftOuter),
    Result = Table.Sort(Table.RemoveColumns(Joined,{"Coach"}),{{"TrainerID",Order.Ascending}})
in
    Result

A left outer join keeps every row from the first table and fills the second table’s columns with null where there is no match.

The result keeps all three trainers:

TrainerID	TrainerName	BookingID	Topic
T-A	Maya	B1	Pivot Tables
T-B	Liam	B2	Charts
T-C	Noor	null	null

Noor has no booking, so BookingID and Topic come back as null.

Example 3: Find rows with no match using LeftAnti

Using the same two tables, you now want only the trainers who have no booking at all.

JoinKind.LeftAnti returns rows from the first table that find no match in the second.

Here is the starting data:

TrainerID	TrainerName
T-A	Maya
T-B	Liam
T-C	Noor

BookingID	Coach	Topic
B1	T-A	Pivot Tables
B2	T-B	Charts

Run the anti-join, then keep just the trainer columns:

let
    Trainers = Excel.CurrentWorkbook(){[Name="Trainers3"]}[Content],
    Bookings = Excel.CurrentWorkbook(){[Name="Bookings3"]}[Content],
    Joined = Table.Join(Trainers,"TrainerID",Bookings,"Coach",JoinKind.LeftAnti),
    Result = Table.SelectColumns(Joined,{"TrainerID","TrainerName"})
in
    Result

An anti-join’s right-side columns are all null by definition, so Table.SelectColumns keeps only the columns that carry data.

The result is the one unmatched trainer:

TrainerID	TrainerName
T-C	Noor

Maya and Liam both have bookings, so they drop out. It is a fast way to spot records that are missing from a related table.

Example 4: Rename a shared column before joining

Here you have a Stores table and a Targets table, and both already have a Region column.

If you join them as-is, the two Region columns collide and the join fails. The fix is to rename one of them first.

Here is the starting data:

StoreID	Region
S1	North
S2	South

StoreID	Region	Target
S1	Northeast	5000
S2	Southwest	7000

Rename the second table’s Region to TargetRegion, then join on StoreID:

let
    Stores = Excel.CurrentWorkbook(){[Name="Stores"]}[Content],
    Targets = Excel.CurrentWorkbook(){[Name="Targets"]}[Content],
    TargetsRenamed = Table.RenameColumns(Targets,{{"Region","TargetRegion"}}),
    Joined = Table.Join(Stores,"StoreID",TargetsRenamed,"StoreID"),
    Result = Table.Sort(Joined,{{"StoreID",Order.Ascending}})
in
    Result

With the second Region renamed, both columns now have unique names and the join goes through.

The result carries both region columns:

StoreID	Region	TargetRegion	Target
S1	North	Northeast	5000
S2	South	Southwest	7000

Each store keeps its own Region alongside the TargetRegion it was matched to.

Things to keep in mind with Table.Join

It keeps the key columns from both tables. When the two keys share a name, an inner join quietly merges them into one (Example 1), but any other join kind throws Expression.Error: A join operation cannot result in a table with duplicate column names. Rename one key first with Table.RenameColumns, or use tables whose keys already differ (Examples 2 and 3).
Row order is not guaranteed. The result order depends on the join algorithm, so add a Table.Sort step when you need a predictable order.
The key columns must already exist. Table.Join does not create them. Make sure key1 and key2 name real columns in their tables.
null matches null. Rows with a null key in both tables will match each other, which can produce surprise rows. Filter out null keys first if that is not what you want.

Performance and query folding

Against a foldable source like SQL Server, a plain Table.Join can fold into a native SQL join, so the work happens at the source.

A custom keyEqualityComparers or a non-default joinAlgorithm usually breaks folding and pushes the join to your machine.

On Excel, CSV, or folder sources it always runs locally, which is fine for the table sizes most worksheets hold.

Common questions about Table.Join

What is the difference between Table.Join and Table.NestedJoin?

Table.Join returns a flat table with both sets of columns already combined. Table.NestedJoin instead adds a single column of matching rows that you then expand with Table.ExpandTableColumn, which is the path the Merge Queries dialog takes.

Which join kind is the default?

JoinKind.Inner, so leaving joinKind off keeps only the rows that match in both tables.

Can I use Table.Join instead of a lookup?

Yes. A join is the table-wide version of a lookup, and it is often how you replace VLOOKUP in Power Query when you want to bring matching columns over in one step rather than one cell at a time.

List of All Power Query Functions

Related Power Query Functions / Articles: