The nightly refresh takes hours
The symptom: the scheduled refresh runs for two or three hours, sometimes times out, and the morning report is stale. The cause is that the model reloads every row of history to pick up one day of changes. Incremental refresh fixes it, if it is set up so the source does the filtering.
How incremental refresh works
You tell the service to keep older data as stored partitions and to reload only a recent window. A policy such as "archive five years, refresh the last seven days" means a nightly refresh touches a week of rows instead of five years. The service creates the partitions on the first refresh and manages them afterwards.
The whole thing depends on one condition: the filter on the date must be pushed down to the source. If Power Query cannot fold the filter into a database query, it pulls the entire table over the network and filters it in memory, and the refresh is as slow as before. Most failed attempts at incremental refresh fail here.
Step by step
1. Create the two parameters
In Power Query, add two parameters named exactly RangeStart and RangeEnd, both of type Date/Time. The names are reserved; the service substitutes real values at refresh time. Give them any sensible current values for development.
2. Filter the fact table on them
Add a filter step on the date column, with one closed and one open end so rows on the boundary are never counted twice.
= Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)Apply it as early in the query as possible, before merges or custom columns that would prevent folding.
3. Check that the filter folds
Right-click the filter step and look for View Native Query. If it is available, the filter is part of the SQL sent to the source. If it is greyed out, something earlier in the query broke folding: a custom function, a transformation the connector cannot translate, or a source that does not fold at all, such as a plain CSV. Fix the query until the step folds, or move the transformation into a database view.
4. Define the policy
In the model view, open incremental refresh on the fact table. Set the archive window (how much history to keep, for example five years) and the refresh window (how much to reload each time, for example seven days, long enough to cover late-arriving corrections). If the source has a reliable last-modified column, enable "detect data changes" on it so the service reloads only partitions that changed.
5. Publish and run the first refresh
Publish the model. The first refresh in the service is a full load that builds the partitions, so expect it to take as long as the old refresh once. Every refresh after that touches the refresh window only. If the first refresh risks timing out, bootstrap the partitions through the XMLA endpoint, which lets you load year by year.
The two traps
- The PBIX cannot be downloaded any more. Once partitions exist in the service, the model cannot be downloaded back to Desktop. Keep the PBIX in source control or a shared location and treat Desktop as the authoring copy. This surprises people the first time, and it is by design.
- The date column type. The filter compares a Date/Time parameter with the column, so the column must be Date/Time too, or the comparison folds badly or not at all. Convert integer date keys to a real date in the source view rather than in Power Query.
What a good result looks like
A model of ordinary size that reloaded ten years nightly in hours usually refreshes in minutes afterwards; the five fixes walkthrough shows a typical before and after for a model of that shape, not a benchmark. The refresh history in the service shows the duration dropping on the first incremental run, which is the moment to screenshot for the person who approved the work.
When incremental refresh is not the answer
If the source does not fold and cannot be changed, the better fix is to land the data in a lakehouse or warehouse first and point Power BI at a gold table that does fold. On Fabric, Direct Lake reads the Delta table without import and the refresh question disappears. Both are a bigger change than a policy, which is why this guide starts with the policy.
Related: incremental refresh in the Power BI FAQ, the glossary entry.
Is View Native Query greyed out and you cannot see why?
Send the query steps. We reply within a working day with which step breaks folding and how to move it.