Json.Document
Reads JSON text, from a file or a web response, and returns it as lists and records that you can expand into a table.
Json.Document(json, optional encoding)Returns: The parsed structure: a record for a JSON object, a list for a JSON array, or a plain value.
What it does
Json.Document reads JSON, the text format most web services use to send data. It turns the text into M values: an object becomes a record and an array becomes a list. You then expand the list into a table, usually with Table.FromRecords or Table.ExpandRecordColumn. Use it for data from an API, such as bank feeds, payment platforms or exchange rates, or for a JSON export from a system.
Syntax and arguments
json: the JSON content as binary. UseWeb.Contents(url)for a web service orFile.Contents(path)for a saved file.encoding(optional): the text encoding of the content. JSON is normally UTF-8, so the default is usually right.
Example
A payment platform returns a list of settled payments as JSON, with one object per payment. The query turns the list into a table for the cash report.
let
Raw = Json.Document(File.Contents("C:\Exports\settlements-2026-03.json")),
Payments = Raw[payments],
AsTable = Table.FromRecords(Payments),
Typed = Table.TransformColumnTypes(AsTable, {{"amount", Currency.Type}, {"settledOn", type date}})
in
Typed
Raw is a record, and Raw[payments] is the list of payment records inside it. Table.FromRecords turns the list into rows, and the type step sets the amount and date columns.
Example: a second, different use
Reading the same JSON from a web service: replace the File.Contents call with Json.Document(Web.Contents("https://api.example.com/settlements")), and handle the sign-in as the service requires.
Things to watch
- Nested objects appear as record columns. Expand them with Table.ExpandRecordColumn, one level at a time.
- Lists inside a row appear as list columns. Expand them with Table.ExpandListColumn.
- A field that is missing in some records becomes null in the table. Check the row count against the source.
- Dates in JSON are often text. Convert them after the table is built.
In business terms
Settlement reports, bank feeds, payment gateway data and exchange rate tables often arrive as JSON. Keeping the raw response as a query step makes it easier to check the table against the source.