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?
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
```Trap 1: Disable the 'Enable load' option for the SalesTable
Disabling Enable load only hides SalesTable from the data model; it does not alter query folding or move the filter upstream. It tempts when trimming model size, but the fix here is a step that folds, such as filtering before the Group By.
Trap 2: Use CALCULATE in DAX to filter
CALCULATE is a DAX function evaluated in the data model after Power Query has loaded the data, so it cannot push filtering to the source. It tempts for measure-level filtering, but the requirement is a Power Query step that folds upstream.
Trap 3: Add a 'Table.Buffer' step after the filter
Table.Buffer caches the table in memory at that point, preventing folding and forcing the filter to run locally after retrieval. It tempts for repeated downstream references, but it blocks the source-side filtering the scenario requires.
- A
Disable the 'Enable load' option for the SalesTable
Why it fails: Disabling Enable load only hides SalesTable from the data model; it does not alter query folding or move the filter upstream. It tempts when trimming model size, but the fix here is a step that folds, such as filtering before the Group By.
- B
Use CALCULATE in DAX to filter
Why it fails: CALCULATE is a DAX function evaluated in the data model after Power Query has loaded the data, so it cannot push filtering to the source. It tempts for measure-level filtering, but the requirement is a Power Query step that folds upstream.
- C
Add a 'Table.Buffer' step after the filter
Why it fails: Table.Buffer caches the table in memory at that point, preventing folding and forcing the filter to run locally after retrieval. It tempts for repeated downstream references, but it blocks the source-side filtering the scenario requires.
- D
Replace the first three lines with a native SQL query that includes the WHERE clause
Embedding the WHERE clause in a native SQL query lets the source database filter rows before Power Query ingests them, satisfying the stem's requirement to push filtering upstream. This avoids loading the full table and applying the filter locally, which is what causes the slow grouping by ProductID.