Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is importing data into Power BI from a web API that returns JSON data. The JSON data contains a nested array of objects representing 'OrderItems' within each 'Order' record. The analyst needs to expand this 'OrderItems' array so that each item in the array becomes a separate row in the main table, while duplicating the parent 'Order' information for each expanded row. Which Power Query transformation concept is required for this scenario?
- AAppending Queries
- BPivoting Columns
- CMerging Queries
- DExpanding Columns
Show answer & explanationAnswer & explanation
Correct answer: D. Expanding Columns
When working with structured columns containing nested tables or lists, the 'Expand' operation in Power Query is used to flatten the data, creating new rows for each item in the nested structure and duplicating the parent row's data.
Why the other options are wrong
- A. Appending queries stacks rows from one table onto another.
- B. Pivoting columns transforms rows into columns, which is the opposite of expanding a nested array into rows.
- C. Merging queries combines columns from two different tables based on a common key.
Expanding Columns (Power Query)
A Power Query transformation used to flatten structured columns (containing Lists or Tables) into new rows or columns in the main table.
- Crucial for working with nested data structures (e.g., JSON, XML).
- Duplicates parent row data for each expanded child item.
- Can expand into new columns or new rows depending on the nested structure.
Memory trick: Nested lists need to expand, to spread their data across the land.