Power QueryExercises

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.ExpandRecordColumn turns a record column into one column per field. Row count is unchanged.
  • Table.ExpandListColumn turns 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
    Flat

Result

idnamecityskuqty
W-1AnaLeedsK-012
W-1AnaLeedsR-021
W-2BenYorkK-015

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.
Functions in this lesson:Json.DocumentTable.FromRecordsTable.ExpandRecordColumnTable.ExpandListColumn

Next 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.