Question

How do I extract JSON data from an API and transform it into a structured table in Power Query?
I am retrieving JSON data from an API and need to convert it into a structured table format in Power Query. What steps should I follow to correctly parse, expand, and transform the nested JSON data while handling dynamic schema variations?

Answer 1

To extract and transform JSON data from an API in Power Query:

 Connect to API & Load JSON

Source = Json.Document(Web.Contents("https://api.example.com/data"))

Expand & Structure Data

• If the root is a list, use Table.FromList(Source).
• If it's a record, use Table.FromRecords({Source}).
• Expand nested records/lists using Table.ExpandRecordColumn().

 Handle Dynamic Schema

• Use Record.ToTable() for varying fields:

Table.FromRecords(List.Transform(Source, each Record.ToTable(_)))

 Convert & Clean Data

• Use Table.TransformColumnTypes() to assign correct types.
• Remove unwanted columns with Table.RemoveColumns().