Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is working with a large dataset in Power Query, and has applied several transformation steps. To optimize performance and reduce memory consumption in the Power BI model, the analyst wants to ensure that only the absolutely necessary columns are loaded and that any intermediate columns used only for transformation (e.g., helper columns for calculations that are no longer needed) are removed before loading to the model. Which Power Query action should be performed to achieve this?
- ASet 'Enable load' to false for the entire query.
- BDisable 'Include in Report Refresh' for the query.
- CUse 'Choose Columns' to select only the required columns.
- DApply 'Remove Rows' to filter out unnecessary data.
Show answer & explanationAnswer & explanation
Correct answer: C. Use 'Choose Columns' to select only the required columns.
The 'Choose Columns' transformation allows you to explicitly select which columns to keep in your query. This effectively removes all other columns, including any intermediate ones, from being loaded into the data model, thereby optimizing performance and memory usage.
Why the other options are wrong
- A. Setting 'Enable load' to false would prevent the entire query from loading into the model, which is not the goal here.
- B. Disabling 'Include in Report Refresh' means the query won't refresh, but it doesn't prevent its columns from being loaded if 'Enable load' is true.
- D. Removing rows filters data vertically (rows), not horizontally (columns).
Choose Columns (Power Query)
A Power Query transformation that allows users to select a subset of columns to keep in the table, effectively removing all unselected columns.
- Crucial for optimizing data model size and performance.
- Removes unnecessary columns, including intermediate helper columns.
- Should typically be one of the final steps in a query for efficiency.
Memory trick: To keep what you need, 'Choose Columns' indeed.