The incremental refresh that never increments

Folding is evaluated from the source downwards. Power Query pushes a step to SQL Server only while every step before it is also translatable. The first step with no SQL translation becomes the boundary: that step and everything after it runs in the Power Query engine, in memory, over the rows the source returned.

• Step 1 (the ModifiedDate filter with RangeStart/RangeEnd) folds.
• Step 2 (Revenue - Cost) folds - simple arithmetic is translated.
• Step 3 (Date.StartOfMonth) has no SQL Server translation. That is the boundary.

Because the RangeStart/RangeEnd predicate sits upstream of a step the source cannot evaluate, the engine must materialise all 45 million rows and then filter them client-side. With a three-year policy split into many partitions, every partition query repeats that full read - which is why the run takes hours rather than minutes.

Proof: in Power Query, right-click the last step and choose View native query. It is available at step 2 and greyed out at step 3 and after. Compare the SQL in that window with what the DBA captured. Note that folding never resumes once broken, so a later type change (step 4) does not restore it.

Also confirm the parameters are typed as date/time and that the filter really is the first step.

Best first:

1. Delete the column and relate the fact to a Date dimension on OrderDate, keeping Year/Month in that dimension. Nothing is computed per row, folding stays intact, and users slice on 'Date'[Month]. This is the default answer unless the column is needed for something a relationship cannot do.
2. Create the column in the source - a persisted computed column or indexed view in SQL Server - so the step becomes a plain column reference that folds. Cost: a DBA change and a schema dependency. Benefit: it also works for DirectQuery.
3. Use foldable extracts instead of a date truncation. Date.Year and Date.Month return integers and are commonly translated; you lose the Date data type and need a sort column, so this only suits labels. Verify with View native query before relying on it.
4. A DAX calculated column on the imported table. It is computed after load, so it cannot break folding - but it adds memory to a 45-million-row table and does not help a DirectQuery table.

Choose option 1. The column duplicates what the Date table already provides, and every extra column adds cardinality to a very large fact. If the column must stay (for example to relate to a planning table), use option 2.

No. Creating or changing an incremental refresh policy rebuilds the partition scheme, so the first refresh must populate every historical partition - three years of data - before boundary partitions exist. Expect roughly the original full-load time plus partition overhead.

Later refreshes should touch only partitions intersecting the last ten days of ModifiedDate. At day granularity that is a handful of small, folded queries, so the run should fall to minutes or seconds.

Verify three things so the improvement is real:

• the refresh history shows an incremental refresh, not a full one;
• the DBA's captured query now carries the RangeStart/RangeEnd predicate;
• nobody triggers a whole-dataset Refresh now from the workspace, which bypasses the policy.

One design note: day granularity over three years creates about 1,100 partitions. Only a limited number process in parallel, so if the archive period grows, switch the historical partitions to month or year granularity and keep days only for the boundary.