Table.ExpandTableColumn

Spreads a column of nested tables into ordinary columns: each nested row becomes a row of its own.

Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.

Syntax

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

Parameter	Type	Description
`table`	table	The table with a column of nested tables.
`column`	text	The column that holds the nested tables.
`columnNames`	list	The nested tables' columns to bring out.
`newColumnNames`	nullable list optional	Names for the new columns, in the same order; the nested columns' own names when left out.

Returns

table. The table with the nested column replaced by the chosen columns, one row per nested row.

Remarks

A row whose nested table is empty stays, with nulls in the new columns.
The new names must not clash with the table's other columns: give newColumnNames when they would.

Examples

One row per order line

let
    Orders = #table({"Order", "Lines"}, {
        {1, #table({"Product", "Units"}, {{"Chair", 4}, {"Lamp", 2}})},
        {2, #table({"Product", "Units"}, {{"Desk", 1}})}})
in
    Table.ExpandTableColumn(Orders, "Lines", {"Product", "Units"})

Order	Product	Units
1	Chair	4
1	Lamp	2
2	Desk	1

After a merge, with new names

let
    Orders = #table({"Order", "Product"}, {{1, "Chair"}, {2, "Desk"}, {3, "Sofa"}}),
    Products = #table({"Product", "Price"}, {{"Chair", 50}, {"Desk", 120}}),
    Merged = Table.NestedJoin(Orders, {"Product"}, Products, {"Product"}, "Products")
in
    Table.ExpandTableColumn(Merged, "Products", {"Price"}, {"Unit price"})

Order	Product	Unit price
1	Chair	50
2	Desk	120
3	Sofa	null

No product row matches the sofa: its price is null.

Table.NestedJoin, Table.AggregateTableColumn, Table.ExpandRecordColumn, Table.ExpandListColumn.

Last updated 8 November 2026 · OpenBI Studio 0.3.79