Downloads
Power Query MData sourcesIntermediate

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.

Syntax
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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics