Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A data engineer is using a Dataflow Gen2 to ingest sales transaction data from a REST API. The API returns data in JSON format, and some fields contain nested arrays of product details. The engineer needs to flatten these nested arrays into separate rows to create a tabular structure suitable for analysis. Which Power Query transformation should be used?
- AExpand Column
- BMerge Queries
- CPivot Column
- DGroup By
Show answer & explanationAnswer & explanation
Correct answer: A. Expand Column
The 'Expand Column' transformation in Power Query is specifically designed to handle nested structures, such as lists or records within a column, and flatten them into new rows or columns, which is exactly what's needed for nested arrays of product details.
Why the other options are wrong
- B. Merge Queries combines two queries based on matching values in one or more columns, not for flattening nested structures within a single column.
- C. Pivot Column transforms rows into columns, typically used for reshaping data from long to wide format, not for flattening nested arrays.
- D. Group By aggregates rows based on common values in specified columns, which is not the goal of flattening nested arrays.
Power Query Expand Column
The 'Expand Column' transformation in Power Query flattens structured data (lists, records, tables) within a column into new rows or columns.
- Used for nested JSON or structured data.
- Creates new rows for each item in a nested list/array.
- Crucial for transforming hierarchical data into tabular format.
Memory trick: Reshape data like clay, molding it to your will.