Flatten nested JSON
Expand records and lists in the right order to flatten JSON.
~12 min read · intermediate · Module 18: Nested data and JSON
By Tahir Nas · Examples run in our simulator · Published · Updated
Before this: Inspect a JSON document before flattening it, M basics: let, records and lists
JSON from web shops nests data: an order holds a customer object and a list of lines. Json.Document turns objects into records and arrays into lists. Flattening is a sequence of expand steps, and the order of those steps decides the shape of the result.
Records expand sideways, lists expand down
Table.ExpandRecordColumnturns a record column into one column per field. Row count is unchanged.Table.ExpandListColumnturns a list column into one row per item, repeating the other columns. Row count grows.
Input file ShopJson
[
{"id": "W-1", "customer": {"name": "Ana", "city": "Leeds"}, "lines": [{"sku": "K-01", "qty": 2}, {"sku": "R-02", "qty": 1}]},
{"id": "W-2", "customer": {"name": "Ben", "city": "York"}, "lines": [{"sku": "K-01", "qty": 5}]}
]
let
Orders = Table.FromRecords(Json.Document(ShopJson)),
Customer = Table.ExpandRecordColumn(Orders, "customer", {"name", "city"}),
Lines = Table.ExpandListColumn(Customer, "lines"),
Flat = Table.ExpandRecordColumn(Lines, "lines", {"sku", "qty"})
in
FlatResult
| id | name | city | sku | qty |
|---|---|---|---|---|
| W-1 | Ana | Leeds | K-01 | 2 |
| W-1 | Ana | Leeds | R-02 | 1 |
| W-2 | Ben | York | K-01 | 5 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
After expanding the list, each row holds a single line record; expand it again to get sku and qty. The result has one row per order line, and order-level values (id, name, city) repeat on each line.
Check the grain
Two orders became three rows. That's correct for a line-level table, and wrong if someone later sums an order-level value such as a shipping charge: it would be counted once per line. Keep order-level amounts in an order-level table, or divide them deliberately.
Two lists in one object
Type the result after flattening: JSON numbers arrive as numbers, but dates and money often arrive as text, and nothing has a declared column type until you set one.
Where to find this in Excel
- Excel for Windows
- The expand icon on a record or list column's header
- What you see
- A list of the record's fields appears; tick the ones that should become columns.
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); Merge queries overview (Microsoft Learn).
Common mistakes
- Expanding two sibling lists in one query and multiplying rows.
- Summing an order-level value after expanding to line level.
- Leaving the flattened columns untyped.
Excel, Power BI and the simulator
- Excel and Power BI Desktop: the expand button on a record column offers field checkboxes; on a list column it offers Expand to New Rows or Extract Values.
- Untick Use original column name as prefix to avoid names like customer.name, unless two nested objects share field names.
- The simulator expands records and lists exactly as shown; JSON comes from fixture files because Web.Contents isn't available.
Table.FromRecordsTable.ExpandRecordColumnTable.ExpandListColumnNext step
Apply this lesson in the simulator. The exercise is checked against fresh data, so the query has to work for rows it hasn't seen. It's part of Pro.
Practise: Flatten nested JSON orders into one row per line (webshop)Practice this
Each exercise runs in the browser and is checked against fresh data, not just the example.
- 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.
- advanced~14 minPro
Flatten nested JSON orders into one row per line (marketplace)
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~8 minPro
Convert JSON records that don't all have the same fields (volunteers)
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
Flatten a JSON object keyed by product code (electronics)
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.
- intermediate~9 minPro
Summarise each order's lines without flattening them (marketplace)
For every JSON order, count its lines and total its units directly from the nested list, keeping one row per order.
- intermediate~8 minPro
Expand a nested record whose field names clash with existing columns (webshop)
Orders have an id, and the nested customer object also has an id. Expand the customer without mixing them up.
- intermediate~8 minPro
Expand a nested record whose field names clash with existing columns (marketplace)
Orders have an id, and the nested customer object also has an id. Expand the customer without mixing them up.
- advanced~13 minPro
Turn a list of name/value attributes into columns (apparel)
Each product has an attributes list like [{name: colour, value: red}, …]. Make one column per attribute name.
- advanced~13 minPro
Turn a list of name/value attributes into columns (furniture)
Each product has an attributes list like [{name: colour, value: red}, …]. Make one column per attribute name.
- intermediate~7 minPro
Build a table from a JSON columns + rows payload (analytics)
A reporting API returns {columns: [...], rows: [[...], ...]}. Turn it into a table with those column names.
- intermediate~7 minPro
Build a table from a JSON columns + rows payload (telemetry)
A reporting API returns {columns: [...], rows: [[...], ...]}. Turn it into a table with those column names.
- advanced~10 minPro
Keep orders whose lines include a gift item (webshop)
Without flattening, keep the JSON orders where any line's sku starts with GIFT, with the customer name.
- advanced~10 minPro
Keep orders whose lines include a gift item (marketplace)
Without flattening, keep the JSON orders where any line's sku starts with GIFT, with the customer name.
- intermediate~8 minPro
Predict how many rows each order becomes when lines are expanded (webshop)
Before running Expand to New Rows on lines, state how many rows each order will turn into.
- intermediate~8 minPro
Predict how many rows each order becomes when lines are expanded (marketplace)
Before running Expand to New Rows on lines, state how many rows each order will turn into.