Power QueryExercises

Inspect a JSON document before flattening it

Read the shape of a JSON document and turn its main array into a table.

~9 min read · beginner · Module 2: Importing and inspecting data

By Tahir Nas · Examples run in our simulator · Updated

Before this: Import CSV files: delimiters, encodings and headers

JSON files from web shops, APIs and apps nest data inside objects and arrays. Before flattening anything, find out what you've got. Json.Document converts the text into M values: a JSON object becomes a record, an array becomes a list, and strings, numbers, true/false and null become the matching M values.

Look at the top level first

Many exports wrap the useful data in an envelope with metadata. Record.ToTable lists a record's fields as Name/Value rows, which is a quick way to see the envelope:

Input file OrdersJson

{
  "exported": "2026-10-31",
  "orders": [
    {"id": "W-1", "customer": {"name": "Ana", "city": "Leeds"}, "total": 42.5},
    {"id": "W-2", "customer": {"name": "Ben", "city": "York"}, "total": 18}
  ]
}
let
    Source = Json.Document(OrdersJson),
    Fields = Record.ToTable(Source)
in
    Fields

Result

NameValue
exported2026-10-31
orders[List]

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

orders is a list: that's the part worth turning into a table.

An array of objects becomes a table

A list of records, one per order, is the most common shape. Table.FromRecords turns it into a table with one column per field. Nested objects stay as record values in their cells until you expand them:

Input file OrdersJson

{
  "exported": "2026-10-31",
  "orders": [
    {"id": "W-1", "customer": {"name": "Ana", "city": "Leeds"}, "total": 42.5},
    {"id": "W-2", "customer": {"name": "Ben", "city": "York"}, "total": 18}
  ]
}
let
    Source = Json.Document(OrdersJson),
    Orders = Table.FromRecords(Source[orders])
in
    Orders

Result

idcustomertotal
W-1[Record]42.5
W-2[Record]18

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

The editor does the same through To Table on a list, followed by an expand button. Writing it as Table.FromRecords is shorter and keeps the field names.

Things to note while inspecting

  • Which fields are records (expand sideways into columns) and which are lists (expand downwards into rows).
  • Whether every object has the same fields. Missing fields become null when you expand, and Table.FromRecords needs consistent fields.
  • Whether numbers arrive as JSON numbers or as strings like "42.50" that still need a type.
  • Dates: JSON has no date type, so dates arrive as text and need converting.

The flatten nested JSON lesson continues from here and expands the customer record and order lines.

Where to find this in Excel

Excel for Windows
Data > Get Data > JSON
Excel for Mac
Data > Get Data (Power Query) > JSON
What you see
JSON is one of the file sources Microsoft lists for Power Query in Excel for Windows, Mac and the web.

Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Where to get data (Microsoft Learn); Import and shape data in Excel for Mac (Power Query) (Microsoft Support); Use Power Query in Excel for the web (Microsoft Support).

Common mistakes

  • Expanding everything at once without checking which fields are lists, which multiplies rows unexpectedly.
  • Assuming JSON dates are dates: they're text until you convert them.
  • Navigating by position (Source{0}) into an envelope whose order isn't guaranteed, instead of by field name (Source[orders]).

Excel, Power BI and the simulator

  • Excel: Data → Get Data → From File → From JSON. Power BI Desktop: Get data → JSON. Both open the editor showing the top-level record or list.
  • For web APIs, hosts use Web.Contents around Json.Document; the simulator doesn't make web requests, so JSON comes from fixture files.
  • In the simulator, record and list cells display as [Record] and [List] until you expand them, just like the native preview.
Functions in this lesson:Json.DocumentRecord.ToTableTable.FromRecords

Next step

Check that the ideas in this lesson stuck. The quiz explains every answer, including the wrong ones.

Take the Importing data quiz