Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataEasy
A data analyst is importing a dataset into Power BI from a SQL Server database. The database contains a table named `SalesOrders` with millions of rows. To improve initial load performance and reduce memory consumption in Power BI Desktop, the analyst wants to retrieve only the data for the current year. Which Power Query transformation should the analyst use to filter the data at the source before loading it into Power BI?
- AAdd Column
- BGroup By
- CFilter Rows
- DMerge Queries
Show answer & explanationAnswer & explanation
Correct answer: C. Filter Rows
Filtering rows in Power Query before loading data into Power BI is crucial for performance, especially with large datasets, as it reduces the amount of data transferred and processed. This transformation applies the filter at the source if query folding is supported, optimizing the data retrieval process. The other options perform different data manipulation tasks.
Why the other options are wrong
- A. Add Column creates a new column derived from existing ones, not for filtering rows.
- B. Group By aggregates rows based on common values, which is not about reducing the initial dataset size based on a condition.
- D. Merge Queries combines two tables based on matching values, which is not the goal here.
Filter Rows (Power Query)
A Power Query transformation that allows users to include or exclude rows from a table based on specified criteria, often improving performance by reducing data loaded.
- Reduces dataset size
- Can enable query folding for source-side filtering
- Essential for performance optimization with large datasets
Memory trick: Fast Data Loads: Filter First, Fold Smartly.