Json.Document Function in Power Query M (4 Easy Examples)

Json.Document parses a piece of JSON text and turns it into Power Query values you can work with, like a record, a list, or a single value.

If you have JSON sitting in a column, a file, or coming back from a web call, this is the function that converts it into something you can shape into a table in Power Query.

Table of Contents

Syntax of Json.Document Function

Json.Document(jsonText as any, optional encoding as nullable number) as any

where

jsonText (required, any). The JSON to parse. This is usually a text value, but it can also be binary, such as the output of File.Contents or Web.Contents.
encoding (optional, nullable number). A TextEncoding.Type value that tells Power Query how to read a binary source. Omit it and Power Query defaults to UTF-8.

Returns: a record, a list, or a single value, depending on the shape of the JSON. A JSON object becomes a record, a JSON array becomes a list, and a primitive becomes a plain value.

In plain terms, you hand it JSON text and it gives you back the matching Power Query value, ready to flatten or expand.

Example 1: Parse a JSON array into a list

You have a small JSON array of numbers and you want it as a Power Query list.

let
    Source = Json.Document("[10,20,30]")
in
    Source

Result: a list with the values 10, 20, 30.

A JSON array always comes back as a list, so each element lines up as a list item.

Example 2: Parse an object and read one field

You have a JSON object and you only need the value of one field.

Here the JSON describes a city, and you want just the city name.

let
    Source = Json.Document("{""city"":""Austin"",""pop"":961855}")[city]
in
    Source

Result: "Austin"

Json.Document returns a record, and the [city] lookup pulls out a single field value.

Example 3: Turn an array of objects into a table

This is the most common real use of Json.Document. You have a JSON array where each item is an object, and you want it as a table.

Each object has the same fields, so wrapping the parsed list in Table.FromRecords lines them up into columns.

let
    Source = Table.FromRecords(Json.Document("[{""name"":""Ann"",""age"":30},{""name"":""Bob"",""age"":25}]"))
in
    Source

Json.Document returns a list of records, and Table.FromRecords stacks those records into rows.

The result is a table:

name	age
Ann	30
Bob	25

Each object becomes a row, and each field name becomes a column header.

Example 4: A JSON object on its own returns a record

If you parse a JSON object without picking a field, you get the whole record back.

let
    Source = Json.Document("{""city"":""Austin"",""pop"":961855}")
in
    Source

Result: a record [city="Austin", pop=961855]

This is why Example 2 added [city]. The record is the native return type for a JSON object, and you read a field from it or expand it from there.

Things to keep in mind with Json.Document

The result is parsed, not flattened. Json.Document only turns JSON into a record, a list, or a value. Shaping that into a usable table is a separate step (field access, Table.FromRecords, or the Expand buttons).
Nested JSON stays nested. If a field holds another object or array, it comes back as a nested record or list that you have to expand again. Real API responses are often several levels deep, so expect more than one expand step.
Field access is case-sensitive. Reading [City] from a record whose field is city throws an error. Match the JSON’s exact casing.
It takes text or binary. Pass JSON text straight in, with no Text.ToBinary needed. The encoding argument only matters when the source is binary, like File.Contents. Inline text defaults to UTF-8.
In real pipelines it’s fed by Web.Contents or File.Contents. The JSON usually arrives from an API response or a file rather than a hard-coded string.
Malformed JSON throws. A missing bracket or stray comma raises an error, so the source has to be valid JSON for the parse to succeed.

Common questions about Json.Document

How do I turn JSON into a table?

Parse the JSON with Json.Document, then convert the result. For an array of objects, wrap it in Table.FromRecords (Example 3). For a single object, read or expand its record fields.

What is the difference between Json.Document and Xml.Document?

Json.Document parses JSON text into records, lists, and values. Xml.Document does the same job for XML, returning a table of the document’s nodes instead.

Do I need Text.ToBinary before Json.Document?

No. Json.Document accepts a text value directly, so converting to binary first is unnecessary. Binary only comes up when the source is already binary, such as File.Contents.

List of All Power Query Functions

Related Power Query Functions / Articles: