Json.Document

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Json.Document

What is the Json.Document Function?

The Json.Document function is used to convert a JSON string into a table of values. This function takes a single parameter, which is the JSON string to be parsed. The output of this function is a table with columns and rows that correspond to the JSON data.

The M Code Behind the Json.Document Function

The M code behind the Json.Document function is relatively simple. The code uses the built-in M functions Text.FromBinary and Json.Document to convert the JSON string into a table of values.

Json.Document = (json as text) =>

let

binaryJson = Text.ToBinary(json),

result = Json.Document(binaryJson)

in

result

The first step in the M code is to convert the JSON string into a binary format using the Text.ToBinary function. This step is necessary because the Json.Document function requires binary input.

The second step in the M code is to call the Json.Document function with the binary JSON input. The output of this function is a table with columns and rows that correspond to the JSON data.

Working with the Json.Document Function

Once you have converted your JSON data into a table of values using the Json.Document function, you can use a variety of Power Query functions to manipulate and transform the data. For example, you can use the ExpandRecordColumn function to expand columns that contain nested JSON records.

let

Source = Json.Document(json),

#”Expanded Column1″ = Table.ExpandRecordColumn(Source, “Column1”, {“Property1”, “Property2”}, {“Column1.Property1”, “Column1.Property2”})

in

#”Expanded Column1″

In this example, the ExpandRecordColumn function is used to expand the "Column1" column, which contains nested JSON records. The function specifies the properties to be expanded and the names of the new columns.

The Json.Document function is a powerful tool that allows you to convert JSON data into a table of values in Power Query. The M code behind this function is relatively simple and can be used in a variety of scenarios. By understanding how the Json.Document function works, you can take full advantage of the power and flexibility of Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)