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
ExpandedResult
| id | name | phone |
|---|---|---|
| C1 | Ana | 0161 000 111 |
| C2 | Ben | null |
| C3 | null | null |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
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
ExpandedResult
| id | name | phone | tier |
|---|---|---|---|
| C1 | Ana | 0161 000 111 | null |
| C2 | Ben | null | Gold |
| C3 | null | null | null |
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.
Table.ExpandRecordColumnRecord.FieldNamesList.UnionList.CombineNext 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