Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is connecting to a web service that returns data in a JSON array format. Each object in the array represents a customer and has nested fields for 'ContactInfo' (containing 'Email' and 'Phone') and 'Address' (containing 'Street', 'City', 'State', 'Zip'). The analyst needs to flatten this structure so that 'Email', 'Phone', 'Street', 'City', 'State', and 'Zip' appear as individual columns in the Power BI data model. Which Power Query transformation should be used to achieve this?
- AMerge Queries
- BAppend Queries
- CUnpivot Columns
- DExpand Record
Show answer & explanationAnswer & explanation
Correct answer: D. Expand Record
When working with nested JSON data, 'Expand Record' is the correct transformation to convert nested records into new columns within the main table. This allows access to the 'Email', 'Phone', and address components directly. Unpivot, Merge, and Append serve different purposes.
Why the other options are wrong
- A. Merge Queries combines two separate tables based on a common key, which is not applicable here.
- B. Append Queries stacks rows from one table onto another, used for combining tables with similar structures.
- C. Unpivot Columns transforms column headers into row values, which is for transposing data, not flattening nested records.
Expand Record (Power Query)
A Power Query transformation used to flatten a column containing structured record values (like those from JSON or nested tables) into new columns, exposing the fields within the record.
- Used for nested data structures (JSON, records)
- Converts a single record column into multiple new columns
- Essential for flattening hierarchical data
Memory trick: Expand Records: Unpack the Nested Tree into a Flat Table.