Expand
Turning a nested table, record or list column into ordinary columns or rows.
Expanding a table column can add rows (one per nested row); expanding a record column adds columns only; expanding a list column adds rows. Selecting just the fields you need keeps the query fast, and naming them avoids clashes with existing columns.
In the Power Query editor
The two-arrow icon in the header of a nested column opens the expand menu.
Learn more
Related functions
Practise it
- advanced~14 minPro
Flatten nested JSON orders into one row per line (webshop)
Parse the JSON, expand the customer object and the list of order lines into a flat table.
- intermediate~8 minPro
Convert JSON records that don't all have the same fields (crm)
Some contacts have no email field and some no phone field at all. Build a table with all four columns.
- intermediate~9 minPro
Flatten a JSON object keyed by product code (garden)
The price feed is one object whose keys are SKUs and whose values are {name, price} objects. Make it a table.
- intermediate~9 minPro
Summarise each order's lines without flattening them (webshop)
For every JSON order, count its lines and total its units directly from the nested list, keeping one row per order.