Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is importing data from a web API that returns customer order details. The API response is in JSON format, and each order record contains a nested object called 'ShippingAddress' which itself contains fields like 'Street', 'City', and 'ZipCode'. To use these address components directly in the Power BI report, what Power Query transformation is required after initially connecting to the JSON source?
- APivot Column
- BParse JSON
- CExtract Text After Delimiter
- DExpand Record
Show answer & explanationAnswer & explanation
Correct answer: D. Expand Record
After parsing JSON, nested objects (records) appear as structured columns. To access the individual fields within the 'ShippingAddress' record (Street, City, ZipCode), the 'Expand Record' transformation is necessary. This flattens the record into new columns.
Why the other options are wrong
- A. Pivot Column transforms rows into columns, which is not the goal here; the goal is to flatten a nested record.
- B. Parse JSON is usually the first step to convert a JSON text string into a structured list or record, but expanding the nested record is a subsequent step.
- C. This transformation is for splitting text strings based on a delimiter, not for handling nested JSON objects.
Expand Record
The 'Expand Record' transformation in Power Query flattens a column containing records (nested objects) into new columns, allowing access to the individual fields within each record.
- Used for nested JSON objects or structured data.
- Converts a record column into multiple columns.
- Makes nested data accessible for reporting.
Memory trick: Expand the box to see all the items inside.