List columns: expand to rows or extract values
Pick the right treatment for a column of lists for the table you need.
~9 min read · intermediate · Module 18: Nested data and JSON
By Tahir Nas · Examples run in our simulator · Updated
Before this: Flatten nested JSON, Lists in depth: build, transform, select and access
A column of lists appears when JSON holds arrays, when Text.Split runs in a custom column, or after grouping with List functions. Three treatments cover nearly every case, and each gives a different table.
Three treatments of the same column
let
Tickets = #table({"Ticket", "TagText"}, {{"T-1", "billing;urgent"}, {"T-2", "login"}, {"T-3", ""}}),
Lists = Table.AddColumn(Tickets, "Tags", each List.Select(Text.Split([TagText], ";"), each _ <> "")),
Rows = Table.ExpandListColumn(Table.RemoveColumns(Lists, {"TagText"}), "Tags")
in
RowsResult
| Ticket | Tags |
|---|---|
| T-1 | billing |
| T-1 | urgent |
| T-2 | login |
| T-3 | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
let
Tickets = #table({"Ticket", "TagText"}, {{"T-1", "billing;urgent"}, {"T-2", "login"}, {"T-3", ""}}),
Lists = Table.AddColumn(Tickets, "Tags", each List.Select(Text.Split([TagText], ";"), each _ <> "")),
Extracted = Table.AddColumn(Lists, "Tag list", each Text.Combine([Tags], ", "), type text),
Counted = Table.AddColumn(Extracted, "Tag count", each List.Count([Tags]), Int64.Type)
in
Table.SelectColumns(Counted, {"Ticket", "Tag list", "Tag count"})Result
| Ticket | Tag list | Tag count |
|---|---|---|
| T-1 | billing, urgent | 2 |
| T-2 | login | 1 |
| T-3 | 0 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
Which to choose
| You need | Treatment |
|---|---|
| Counts or filters per tag (how many urgent tickets?) | Expand to rows, in its own table |
| A readable label per ticket | Extract values |
| A number per ticket (tag count, has urgent?) | Summarise with a list function |
Expanding to rows changes the grain of the table, so keep it in a separate query if the main table should stay one row per ticket. A ticket-to-tag table linked by Ticket is the clean model for Power BI.
Split into rows directly
Split Column → By Delimiter → Advanced → Split into Rows does the split and expand in one go. It's the same pattern: a list per cell, then Table.ExpandListColumn.
Where to find this in Excel
- Excel for Windows
- Home > Split Column > By Delimiter > Advanced options > Split into Rows
- What you see
- Each delimited value becomes its own row and the other columns repeat.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Split columns by delimiter (Microsoft Learn).
Common mistakes
- Expanding a list column in the main fact table and multiplying its amounts.
- Forgetting that splitting empty text gives a list with one empty item; filter empties out.
- Using Extract Values when the analysis needs to count or filter individual items.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: the expand button on a list column offers Expand to New Rows and Extract Values… (with a delimiter choice).
- Extract Values writes Table.TransformColumns with Text.Combine; it fails on lists that contain numbers unless they're converted to text first.
- The simulator supports Table.ExpandListColumn, including empty lists, which produce a single null row as shown.
Table.ExpandListColumnText.SplitText.CombineList.CountNext step
Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.
Take the Nested data and JSON quiz