Because JSON responses frequently contain hierarchical structures like objects within arrays, arrays of objects, or deeply nested fields, converting nested JSON API data into a tabular format in Power BI is a frequent challenge. The Power Query editor in Power BI offers versatile tools for shaping and flattening this data for reporting.
Join the JSON Source
Make use of the JSON file connector or Get Data > Web.
Power BI loads the complete JSON into a single column called Value, which is usually of type Record or List.
Utilize the "List" and "Record" tools.
To reveal the column's fields as columns, click the Expand icon if the column is a Record.
Click the To Table button and then expand the resultant record if the column is a list.
Iteratively Extend Nested Structures
Multiple nesting layers may be present in JSON. Step by step, keep expanding each nested Record or List.
Until all pertinent fields are displayed in tabular form, use the "Expand Columns" button (which has an arrow icon).
Headers should be renamed and promoted.
To tidy up after expansion, select Transform > Use First Row as Headers and Rename Columns.
Handle Dynamic or Optional Fields
Some fields might be present only in certain records.
Use Column profiling or conditional logic (Record.HasFields, try...otherwise) to handle inconsistent structures gracefully.
