Json.Document
Record function · By Tahir Nas · Examples run in our simulator · Published · Updated
Parses JSON text into M records and lists.
When to use it
Use it to parse JSON from an API response, a file or a text column into records and lists, then turn them into a table with Table.FromRecords or by expanding.
Syntax
Json.Document(jsonText as any, optional encoding as nullable number) as anyReturns
A record, list or value.
Example
Json.Document("{""name"": ""Ana"", ""tags"": [1, 2]}")[name]Result
Ana
Example: an array of objects to a table
A JSON array of objects becomes a list of records, which Table.FromRecords turns into rows.
Table.FromRecords(Json.Document("[{""id"": 1, ""city"": ""Porto""}, {""id"": 2, ""city"": ""Lyon""}]"))Result
| id | city |
|---|---|
| 1 | Porto |
| 2 | Lyon |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Objects become records, arrays become lists.
Common mistakes
- Expanding before checking that every object has the same fields.
- Treating a JSON object as if it were an array of rows.
Exercises that use Json.Document
- beginner~5 minFree
Turn a JSON list of accounts into a table (saas)
A JSON file holds a list of flat account records. Convert it to a table with one row per account.
- advanced~14 minPro
Flatten nested JSON orders into one row per line (webshop)
Parse the JSON, expand the customer object and the list of order lines into a flat table.
- intermediate~10 minPro
Combine a folder of JSON files (marketplace)
A partner drops one JSON file per day, each a list of order records. Combine them all with the file name.
- beginner~6 minPro
Turn a settings object into a Name/Value table (report config)
A JSON settings file is a single object. List its settings as rows with Name and Value columns.
- intermediate~8 minPro
Convert JSON records that don't all have the same fields (crm)
Some contacts have no email field and some no phone field at all. Build a table with all four columns.
- intermediate~9 minPro
Flatten a JSON object keyed by product code (garden)
The price feed is one object whose keys are SKUs and whose values are {name, price} objects. Make it a table.
Learn more
- Flatten nested JSON lesson
Related functions
Official documentation: Json.Document on Microsoft Learn.