Table.ExpandTableColumn
Opens a column of nested tables and turns the chosen columns into normal columns, one row for each nested row.
Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as tableReturns: 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
- table: the table with the nested column. Required.
- column: the name of the nested column, in quotes. Required.
- columnNames: a list of the columns to bring across from the nested table, in braces. Required.
- newColumnNames: a list of new names for those columns, in the same order. Optional, and useful to avoid clashes.
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
- Expanding a match with several rows repeats the main row for each match. Check the row count before and after.
- Name the new columns to avoid clashes with columns that already exist.
- Expand only the columns the report needs, to keep the table narrow.
- Query folding: an expansion that follows a merge on the same database usually folds with the join. Check View Native Query.
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.