Downloads
Power Query MTable: combine and joinAdvanced

Table.Join

Joins two tables on matching key columns and returns one flat table with the columns of both.

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 keyComparer as nullable function) as table

Returns: A flat table with the columns of both tables.

What it does

Table.Join matches rows from two tables on a key, such as an account code, and puts the columns of both side by side in one flat table. The join kind decides which rows are kept. Table.NestedJoin is the gentler form, because it keeps the second table in a column that you expand only when you want it. Table.Join is the one to use when you want every column at once.

Syntax and arguments

Example

A general ledger is matched to a chart of accounts, to bring the account name and type into every posting. The chart's Account column is renamed first, so the flat join has no duplicate column names.

let
    GL = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    Chart = Excel.CurrentWorkbook(){[Name = "ChartOfAccounts"]}[Content],
    ChartKeys = Table.RenameColumns(Chart, {{"Account", "AccountCode"}}),
    WithNames = Table.Join(GL, "Account", ChartKeys, "AccountCode", JoinKind.LeftOuter)
in
    WithNames

Every GL row keeps its own columns, and the account name and type come across from the chart. A posting with an account not in the chart keeps blank values for those columns.

Example: a second, different use

A reviewer wants only the postings that have a matching account. An inner join removes postings whose account is not in the chart.

let
    GL = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    Chart = Table.RenameColumns(Excel.CurrentWorkbook(){[Name = "ChartOfAccounts"]}[Content], {{"Account", "AccountCode"}}),
    Matched = Table.Join(GL, "Account", Chart, "AccountCode", JoinKind.Inner)
in
    Matched

Things to watch

In business terms

Joining a general ledger to a chart of accounts, invoices to customer details or bank lines to a payment list are everyday reconciliation and reporting steps. Choosing the right join kind decides which exceptions you see.

Related functions

All Power Query M functions · View as Markdown · M basics