A data analyst is importing a dataset into Power BI from a SQL Server database. The database contains sensitive customer information, and for a specific report, only a subset of the columns from a very large table is needed. To optimize performance and reduce data transferred, the analyst wants to ensure that Power Query pushes down the column selection operation to the source database. Which Power Query transformation is crucial for achieving this 'query folding' for column selection?
- ARemove Other Columns
- BChoose Columns
- CAdd Column From Examples
- DReorder Columns
Show answer & explanationAnswer & explanation
Correct answer: B. Choose Columns
The 'Choose Columns' transformation (or selecting/deselecting columns in the UI) is a key operation that Power Query can fold back to the source database, effectively translating it into a `SELECT` statement that retrieves only the necessary columns, thus optimizing data transfer and performance.
Why the other options are wrong
- A. While 'Remove Other Columns' achieves the same visual result, 'Choose Columns' is often the explicit choice that is more reliably folded for column selection.
- C. Adding columns from examples creates new columns in Power Query and typically breaks query folding for subsequent steps, as it's a client-side operation, not a source-side optimization.
- D. Reordering columns changes the display order in Power Query but does not affect the columns selected from the source database, hence it has no impact on query folding for column selection.
Choose Columns (Power Query) & Query Folding
The 'Choose Columns' transformation explicitly selects which columns to retain. When applied to foldable data sources (like SQL databases), Power Query can translate this operation into a native query (e.g., a SQL SELECT statement), reducing data transferred and improving performance through 'query folding'.
- Selects a subset of columns to keep.
- Directly supports query folding for relational databases.
- Reduces data volume fetched from source.
Memory trick: Folding queries is like sending a precise shopping list to the database, not buying the whole store.