Table.ExpandListColumn in Power Query M (5 Examples)

Table.ExpandListColumn splits a list column into one row per list value and copies the other columns into each new row. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you have a column where each cell holds a list and you want one row per value instead, this is the function to reach for.

Table of Contents

Syntax of Table.ExpandListColumn Function

Table.ExpandListColumn(table as table, column as text) as table

where

table (required, table). The table that contains the list column.
column (required, text). The name of the column whose lists you want to expand into rows.

Returns: a table with one row for each value in the lists of column. Values in the other columns are duplicated into every new row. An empty list or a null cell keeps its row and shows null in the expanded column.

In plain terms, you point it at the column that holds lists and it unpacks each list downward, one row per item.

Example 1: Expand a list column into rows

Say a Workshop column has one row per session, and the Attendees cell holds a list of names.

Here is the starting data:

Workshop	Attendees
Excel Basics	{Priya, Daniel, Moira}
Charts Deep Dive	{Ken}

Expand the Attendees column:

let
    Source = #table({"Workshop","Attendees"},{{"Excel Basics",{"Priya","Daniel","Moira"}},{"Charts Deep Dive",{"Ken"}}}),
    Expanded = Table.ExpandListColumn(Source,"Attendees")
in
    Expanded

Each name gets its own row, and the workshop name repeats next to it.

The result has one row per attendee:

Workshop	Attendees
Excel Basics	Priya
Excel Basics	Daniel
Excel Basics	Moira
Charts Deep Dive	Ken

The three-name list became three rows, and the one-name list became one row.

Example 2: Split a delimited column into rows

Worksheet cells cannot hold lists, so in real data the list usually starts as delimited text. Build the list with Text.Split first, then expand.

Here is the starting data, from a worksheet table named Crew:

Employee	Certifications
Rosa Mendes	First Aid;Forklift;Crane
Theo Park	Forklift
Amara Diallo	First Aid;Welding

Split on the semicolon, then expand:

let
    Source = Excel.CurrentWorkbook(){[Name="Crew"]}[Content],
    SplitToList = Table.TransformColumns(Source,{{"Certifications",each Text.Split(_,";")}}),
    Expanded = Table.ExpandListColumn(SplitToList,"Certifications")
in
    Expanded

Table.TransformColumns turns each text value into a list, which Table.ExpandListColumn then unpacks into rows.

The result has one row per certification:

Employee	Certifications
Rosa Mendes	First Aid
Rosa Mendes	Forklift
Rosa Mendes	Crane
Theo Park	Forklift
Amara Diallo	First Aid
Amara Diallo	Welding

This split-then-expand pattern is the most common real-world use of the function.

Example 3: Create one row per unit with a generated list

You can also generate the list yourself. Here each order needs one packing label per unit, so we want Qty rows per order.

Here is the starting data, from a worksheet table named Orders:

OrderID	Qty
ORD-301	2
ORD-302	3
ORD-303	1

Add a list column with List.Numbers, then expand it:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    AddLabels = Table.AddColumn(Source,"LabelNo",each List.Numbers(1,[Qty])),
    Expanded = Table.ExpandListColumn(AddLabels,"LabelNo")
in
    Expanded

Table.AddColumn builds a list like {1,2,3} for each row, and expanding it duplicates the row that many times.

The result has one numbered row per unit:

OrderID	Qty	LabelNo
ORD-301	2	1
ORD-301	2	2
ORD-302	3	1
ORD-302	3	2
ORD-302	3	3
ORD-303	1	1

This is the standard way to repeat a row N times in Power Query.

Example 4: Handle empty lists and nulls

A common worry with expanding is losing rows where the list is empty or missing. You don’t. Here is proof.

Here is the starting data:

Ticket	Tags
TK-88	{urgent, billing}
TK-89	{}
TK-90	null

Expand the Tags column:

let
    Source = #table({"Ticket","Tags"},{{"TK-88",{"urgent","billing"}},{"TK-89",{}},{"TK-90",null}}),
    Expanded = Table.ExpandListColumn(Source,"Tags")
in
    Expanded

The two-tag list expands to two rows. The empty list and the null cell each produce a single row with null.

The result keeps all three tickets:

Ticket	Tags
TK-88	urgent
TK-88	billing
TK-89	null
TK-90	null

So you can expand a patchy column without losing any records. Filter out the null rows afterwards if you do not want them.

Example 5: Expand a nested table column

Table.ExpandListColumn can also expand a column of nested tables, because it treats each nested table as a list of records.

Here is the starting data, where each Stock cell holds a nested table of items and units:

Warehouse	Stock
Leeds	Table (Drill 14, Sander 6)
Hull	Table (Drill 9)

Expand to rows first, then flatten the records into columns:

let
    Source = #table({"Warehouse","Stock"},{{"Leeds",#table({"Item","Units"},{{"Drill",14},{"Sander",6}})},{"Hull",#table({"Item","Units"},{{"Drill",9}})}}),
    ToRows = Table.ExpandListColumn(Source,"Stock"),
    Flat = Table.ExpandRecordColumn(ToRows,"Stock",{"Item","Units"})
in
    Flat

After Table.ExpandListColumn, the Stock column holds one record per row. Table.ExpandRecordColumn then splits those records into Item and Units columns.

The result is one flat table:

Warehouse	Item	Units
Leeds	Drill	14
Leeds	Sander	6
Hull	Drill	9

If a nested table column is your starting point, Table.ExpandTableColumn does both steps in one call.

Things to keep in mind with Table.ExpandListColumn

The expanded column comes back as type any. Set the type explicitly after expanding (for example with Table.TransformColumnTypes), or sorting and filtering on that column can act up later.
A wrong column name throws an error. Expression.Error: The column 'X' of the table wasn't found. Column names are case-sensitive, so check capitalization too.
Non-list values pass through unchanged. A cell holding a plain value instead of a list keeps its row as-is, so a mixed column expands without errors.
The UI writes this function for you. Click the expand arrow on the column header and choose Expand to New Rows. Useful for checking your hand-written step.

Performance and query folding

List columns are almost always built inside M with steps like Text.Split, so this step does not fold back to the source. It runs locally, and the row count multiplies by the average list length. On large tables, filter rows and remove unneeded columns before expanding to keep that growth cheap.

Common questions about Table.ExpandListColumn

What is the difference between Table.ExpandListColumn and Table.ExpandTableColumn?

Table.ExpandListColumn puts each list item on its own row in the same column. Table.ExpandTableColumn flattens a nested table column into rows and new columns in one step, replacing the two-step pattern from Example 5.

Can I expand two list columns at once?

No, it takes a single column name. Call it once per column, and watch the row count: expanding a second list column multiplies the rows again, giving every combination.

List of All Power Query Functions

Related Power Query Functions / Articles: