Power QueryExercises

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
    Rows

Result

TicketTags
T-1billing
T-1urgent
T-2login
T-3null

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Expand to new rows: one row per tag. T-3 has an empty list and keeps one row with null.
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

TicketTag listTag count
T-1billing, urgent2
T-2login1
T-30

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Extract values joins each list into text; summarise with List.Count keeps one row per ticket.

Which to choose

You needTreatment
Counts or filters per tag (how many urgent tickets?)Expand to rows, in its own table
A readable label per ticketExtract 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.
Functions in this lesson:Table.ExpandListColumnText.SplitText.CombineList.Count

Next 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