Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is importing data from a web API that returns product information in JSON format. The JSON data includes nested records for 'SupplierInfo' (e.g., {'SupplierName': 'ABC', 'SupplierID': '123'}). The analyst needs to expand this 'SupplierInfo' record into new columns named 'SupplierName' and 'SupplierID' in the main table. Which Power Query transformation should be used?
- APivot Column
- BMerge Queries
- CExpand Record
- DAdd Custom Column
Show answer & explanationAnswer & explanation
Correct answer: C. Expand Record
When a column contains records (often from JSON or structured data), the 'Expand Record' transformation allows you to extract fields from that record and create new columns in the main table for each field.
Why the other options are wrong
- A. Pivot Column transforms rows into columns based on unique values in a column, which is unrelated to expanding nested records.
- B. Merge Queries combines two separate queries (tables) based on a common column, which is not applicable for expanding a nested record within a single table.
- D. Add Custom Column creates a new column using a formula, not directly for expanding nested records into multiple columns.
Expand Record (Power Query)
A Power Query transformation used when a column contains structured records (e.g., from JSON or nested tables). It extracts the fields from these records and creates new columns for each field in the primary table.
- Commonly used with JSON or structured data sources.
- Allows selection of which fields to expand.
- Transforms vertical record data into horizontal columns.
Memory trick: Records in a column? Expand them out, make new columns, no doubt!