Parse a JSON String Column in Power Query M

Notes

ParsedJson will contain records or lists depending on the JSON's shape; expand it the same way you would after Json.Document(Web.Contents(...)).

Requirements

• A text column containing valid JSON strings.

How to use this snippet

1. In Power BI Desktop, choose Transform data to open Power Query, then open the Advanced Editor on a query (or start from a blank query).
2. Paste the snippet and replace the placeholder names (tables, columns, file paths) with your own. Check the requirements above first.
3. Run it through the Power Query M Formatter to tidy the layout.

Related Power Query M snippets

• Retrieve Data from a REST APICalls a JSON REST API and converts the response into a table.
• Parse XML DataLoads and parses an XML file or string into a navigable table structure.
• API with Bearer Token HeaderCalls a REST API that requires an Authorization: Bearer token header for authentication.
• Expand Nested JSON RecordsExpands a column of nested record values, common after parsing JSON from an API, into individual columns.
• Expand All Nested Columns DynamicallyDetects every record-type column in a table and expands all of them in one pass, without naming them individually.

More Power Query M resources

Browse all 145 snippets in the Power Query M Code Snippets, or search the library for something similar to Parse a JSON String Column.