Table.ExpandListColumn
Gives each item of a column of lists a row of its own, the row's other columns repeated.
Group: Table. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
Table.ExpandListColumn(table as table, column as text) as table| Parameter | Type | Description |
|---|---|---|
table | table | The table with a column of lists. |
column | text | The column that holds the lists. |
Returns
table. One row per list item, in the lists' order.
Remarks
- A row whose list is empty stays, with null in the column.
Examples
One row per product of an order
let
Orders = #table({"Order", "Products"}, {{1, {"Chair", "Lamp"}}, {2, {"Desk"}}, {3, {}}})
in
Table.ExpandListColumn(Orders, "Products")| Order | Products |
|---|---|
| 1 | Chair |
| 1 | Lamp |
| 2 | Desk |
| 3 | null |
After splitting a text into a list
let
Orders = #table({"Order", "Products"}, {{1, "Chair;Lamp"}, {2, "Desk"}}),
Lists = Table.TransformColumns(Orders, {"Products", Splitter.SplitTextByDelimiter(";")})
in
Table.ExpandListColumn(Lists, "Products")| Order | Products |
|---|---|
| 1 | Chair |
| 1 | Lamp |
| 2 | Desk |
Related functions
Table.ExpandRecordColumn, Table.ExpandTableColumn, Table.SplitColumn.