How to Improve Performance by Pushing Filters to the Source Database
Exhibit
Refer to the exhibit.
```
M query:
let
Source = Sql.Database("myserver.database.windows.net", "SalesDB"),
SalesTable = Source{[Schema="dbo",Item="Sales"]}[Data],
FilteredRows = Table.SelectRows(SalesTable, each [OrderDate] >= #date(2023,1,1)),
GroupedRows = Table.Group(FilteredRows, {"ProductID"}, {{"TotalSales", each List.Sum([Amount]), type number}})
in
GroupedRows
```You are troubleshooting a Power Query transformation that groups sales data by ProductID. The query runs slowly and you suspect the filter is being applied after loading all rows. What change would improve performance by pushing the filter to the source?
Quick Answer
Replacing the query's early steps with a native SQL query that includes a WHERE clause is the answer because it moves the filtering work to the database itself, which can use its own indexes and query engine to eliminate rows before anything is ever sent to Power Query. The scenario describes a classic symptom of broken query folding: a group-by operation on ProductID that runs slowly because the filter is being applied only after every row has already been pulled across the network and loaded locally, meaning the database did none of the heavy lifting. Query folding is Power Query's ability to translate its own steps back into the source's native query language so the source does the work, but folding can break partway through a series of steps, leaving later filters to run locally and slowly instead. Writing the WHERE clause directly into a native SQL query sidesteps that fragility entirely by guaranteeing the filter executes at the source regardless of what happens to folding afterward. Whenever a Power Query transformation is described as slow specifically because filtering seems to happen after all the data has already loaded, think about where query folding might be breaking down, and consider pushing the filter logic explicitly to the source rather than trusting Power Query to fold it there automatically.
⚠ Common exam trap
A common mix-up: candidates confuse in-memory buffering (Table.Buffer) or DAX filter functions with source-level query pushdown, failing to recognize that only native SQL or folding-compatible M steps can reduce data transfer from the source.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Replace the first three lines with a native SQL query that includes the WHERE clause
Pushing filter logic to the source database via a native SQL query with a WHERE clause reduces the amount of data loaded into Power Query. This leverages query folding, which allows the source (e.g., SQL Server) to perform the filtering before data is transferred, significantly improving performance for large datasets.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Disable the 'Enable load' option for the SalesTable
Why it's wrong here
Disabling load would not load any data, which is not desired.
- ✗
Use CALCULATE in DAX to filter
Why it's wrong here
DAX is not used in Power Query; it's for measures.
- ✗
Add a 'Table.Buffer' step after the filter
Why it's wrong here
Buffer does not push filter to source; it caches after loading.
- ✓
Replace the first three lines with a native SQL query that includes the WHERE clause
Why this is correct
Native SQL query allows the database to apply the filter before returning data.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are reviewing a Power Query that imports data from SQL Server. The exhibit shows the M code. The SQL query filters records after a date, then Power Query filters rows with OrderQty > 10, and then groups by ProductID. What is a potential performance issue with this approach?
medium- A.The query will fail because the SQL query uses '>' with a string.
- B.The SQL query should use a parameter for the date instead of a hardcoded value.
- ✓ C.The filter on OrderQty > 10 should be included in the SQL query to reduce the amount of data transferred.
- D.The grouping should be done in SQL to reduce data volume.
Why C: Pushing the `OrderQty > 10` filter into the SQL query reduces the amount of data transferred from SQL Server to Power Query. In Power Query, data is loaded into memory before transformations; filtering earlier in the source query minimizes memory usage and network latency, which is a key performance optimization in Power BI data loading.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.