Power QueryExercises

Expand record columns safely when fields vary

Expand records with optional fields without losing new fields or failing on null.

~10 min read · advanced · Module 18: Nested data and JSON

By Tahir Nas · Examples run in our simulator · Updated

Before this: Flatten nested JSON, Records in depth: fields, defaults and combining

Real JSON is irregular. One customer has a phone, another doesn't; next month a loyalty tier appears. Table.ExpandRecordColumn takes a fixed list of field names, so it needs care on both counts.

Missing fields become null

let
    Customers = #table({"id", "info"}, {
        {"C1", [name = "Ana", phone = "0161 000 111"]},
        {"C2", [name = "Ben"]},
        {"C3", null}
    }),
    Expanded = Table.ExpandRecordColumn(Customers, "info", {"name", "phone"})
in
    Expanded

Result

idnamephone
C1Ana0161 000 111
C2Bennull
C3nullnull

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

Ben has no phone and C3 has no record at all; both expand to nulls rather than errors.

New fields are ignored

The field list was written when the data had name and phone. A tier field added later isn't in it, so it's silently dropped. The editor's expand button writes the list it saw in the preview, so this happens without anyone typing a list.

Build the field list from the data

Collect the field names of every record in the column and expand by that union. Null records are skipped with List.RemoveNulls:

let
    Customers = #table({"id", "info"}, {
        {"C1", [name = "Ana", phone = "0161 000 111"]},
        {"C2", [name = "Ben", tier = "Gold"]},
        {"C3", null}
    }),
    Fields = List.Union(List.Transform(List.RemoveNulls(Customers[info]), Record.FieldNames)),
    Expanded = Table.ExpandRecordColumn(Customers, "info", Fields)
in
    Expanded

Result

idnamephonetier
C1Ana0161 000 111null
C2BennullGold
C3nullnullnull

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

List.Union keeps the first-seen order of names and removes duplicates, so the columns come out in a sensible order.

Dynamic or fixed?

A dynamic field list is right for exploration and for staging tables that should carry everything. For a report with fixed columns, keep an explicit list, and add a check that compares it with the union so you hear about new fields instead of losing them silently.

Where to find this in Excel

Excel for Windows
The expand icon on a record column's header
What you see
You tick which of the record's fields become columns, and the step stores the names you ticked.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Grouping or summarizing rows (Microsoft Learn).

Common mistakes

  • Relying on the expand button's field list, which reflects only the preview rows.
  • Assuming a missing field causes an error; it quietly becomes null.
  • Building a dynamic list for a fixed-layout report, so a new field changes the output columns.

Excel, Power BI and the simulator

  • Excel and Power BI Desktop: the expand dialog lists fields found in the preview; a Load more link scans more rows. It still writes a fixed list.
  • Very wide unions from messy JSON can produce hundreds of columns; select the ones you need after expanding.
  • The simulator's Table.ExpandRecordColumn fills missing fields and null records with null, like the native function.
Functions in this lesson:Table.ExpandRecordColumnRecord.FieldNamesList.UnionList.Combine

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