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 API response contains a list of products, where each product is an object with properties like 'ProductID', 'ProductName', and a nested 'Category' object (e.g., `"Category": {"CategoryID": 1, "CategoryName": "Electronics"}`). The analyst needs to access 'CategoryName' and display it as a top-level column in the product table. Which Power Query operation is used to achieve this?
- AExtract Values
- BParse JSON
- CAdd Custom Column
- DExpand Column
Show answer & explanationAnswer & explanation
Correct answer: D. Expand Column
The 'Expand Column' operation is specifically designed to navigate into nested structured columns (like records or lists) and bring their internal fields up as new top-level columns in the current table.
Why the other options are wrong
- A. Extract Values is used for list-type columns to combine items, not for accessing fields within a record.
- B. Parse JSON is used to convert a text column containing JSON strings into a structured format (record or list), which is typically done before expanding, not as the expansion itself.
- C. While a custom column could use M functions to access nested fields, 'Expand Column' is the direct, user-friendly, and often more performant way to do this for structured columns.
Expand Column (Power Query)
A Power Query transformation that allows you to navigate into a structured column (containing records, lists, or tables) and select its internal fields or elements to be promoted as new top-level columns in the current table.
- Used for nested data structures (records, lists, tables).
- Promotes internal fields to new columns.
- Crucial for flattening complex data sources like JSON/XML.
Memory trick: Expand is like opening a box and taking out what's inside to put it on the shelf.