Downloads
Power Query MTable: combine and joinIntermediate

Table.ExpandTableColumn

Opens a column of nested tables and turns the chosen columns into normal columns, one row for each nested row.

Syntax
Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table

Returns: A table with the chosen columns from the nested tables.

What it does

After a merge with Table.NestedJoin, the matched rows sit inside one column as a table. Table.ExpandTableColumn opens that column and brings the columns you name into the main table as ordinary columns. It is the second half of a merge, and it is where you choose what the report gets from the lookup.

Syntax and arguments

Example

A general ledger is merged with a chart of accounts. The account name and account type are expanded into the ledger, and they are given clear names.

let
    GL = Excel.CurrentWorkbook(){[Name = "GL"]}[Content],
    Chart = Excel.CurrentWorkbook(){[Name = "ChartOfAccounts"]}[Content],
    Merged = Table.NestedJoin(GL, {"Account"}, Chart, {"Account"}, "Chart", JoinKind.LeftOuter),
    Expanded = Table.ExpandTableColumn(Merged, "Chart", {"Account Name", "Account Type"}, {"Account name", "Account type"})
in
    Expanded

Each posting now carries the account name and type from the chart. The original Chart column is removed by the expansion.

Things to watch

In business terms

Adding account names, customer names and budget owners to a list of transactions is the everyday purpose of a merge and its expansion. Choosing the columns explicitly keeps the report's content deliberate.

Related functions

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