Incremental Refresh in Power BI: When You Need It and How to Set It Up Properly

The refresh that took twelve minutes in March takes ninety today, and last Tuesday it timed out. Nothing changed except what always changes: another month of transactions in tables the refresh insists on reloading in full, every night, forever.

Incremental refresh is the canonical fix - stop reprocessing history that has not moved - and it is also the Power BI feature with the largest gap between the documentation and production reality. Configured well, it cuts refresh windows from hours to minutes; configured naively, it silently does nothing while appearing to work.

This guide covers how it actually works, the prerequisite that decides everything, and the gotchas the tutorials skip.

Key Takeaways

• Incremental refresh partitions history so only recent data reloads - the architecture fix for blown-out refresh windows.

• Query folding is the non-negotiable prerequisite - without it, the filters run after the full download, achieving nothing.

• Production needs XMLA awareness: the first publish is a full load, and partitions sometimes need direct management.

What Does Incremental Refresh Actually Do?

It divides a large table into partitions by date and refreshes only the recent ones, archiving the rest untouched. A table holding five years of sales stops being one monolithic reload and becomes sixty monthly partitions, of which the refresh touches one or two. The work shrinks to match what actually changed.

The configuration expresses two policies: how much history to store (say, five years) and how much to refresh (say, the last seven days). Power BI generates and manages the partitions automatically from those two numbers - which is the feature's elegance and, when assumptions break, the source of its surprises.

How Do RangeStart and RangeEnd Actually Work?

They are two date-time parameters you create in Power Query and use to filter the large table; the service then substitutes real boundary values per partition at refresh time. Your single filtered query becomes the template from which every partition's query is stamped.

The setup, in order: create RangeStart and RangeEnd as date-time parameters; filter the table's date column where the value is greater-than-or-equal-to RangeStart and strictly less-than RangeEnd (one boundary inclusive, one exclusive - both inclusive duplicates rows at the seams); then define the incremental policy on the table in the model. The filter must sit early in the query and, critically, must reach the source as a real filter - which is the folding question.

Query folding is the non-negotiable prerequisite - without it, nothing folds.

Why Is Query Folding the Whole Game?

Because the partition filters only save work if the source database applies them - and that only happens when the query folds back to native source language. If folding is broken, the source ships the entire table over the wire and Power Query discards the unwanted rows afterwards: same transfer, same duration, same timeout, now wearing an incremental badge.

The verification takes thirty seconds and is skipped constantly: in Power Query, right-click the final applied step and check View Native Query. Available means folding; greyed out means the chain broke somewhere above. The usual culprits are transformation steps the source cannot translate - certain text operations, added columns with complex logic, or merges against non-foldable sources - and the fix is reordering the steps so the date filter folds before the breakage, or moving the offending logic downstream. Sources that cannot fold at all (flat files, some APIs) cannot benefit from true incremental refresh, whatever the dialog implies.

What Are the Production Gotchas the Tutorials Skip?

Five recur. The first publish triggers a full historical load - schedule it for a quiet window and expect it to be the longest refresh the model ever runs. Folding can break silently later when someone edits the query - re-verify after any change. Partitions occasionally need direct attention via the XMLA endpoint - a botched load or a late schema change is repaired per-partition rather than by full reload. Late-arriving data needs detect-data-changes or a wider refresh window, or last week's corrections never land. And downloading the PBIX stops being possible once the service manages partitions - source control of the file becomes source control of the definition.

None of these is a defect; they are the operational reality of a model that has become a managed, partitioned object rather than a file. Teams that treat it accordingly - with deployment pipelines and a named owner - run incremental refresh uneventfully for years. This operational layer is exactly what a managed services arrangement absorbs for businesses without a dedicated BI engineer.

When Is Incremental Refresh the Wrong Answer?

When the table is small enough that full refresh is already fast - partition machinery on a half-million-row table is complexity without payoff. When the real problem is a transformation bottleneck - a query spending forty minutes in unfoldable logic needs the logic fixed, not partitioned. And when genuine intraday freshness is the requirement - that is hybrid tables (recent data in DirectQuery, history in Import) or, on Fabric, Direct Lake, where the refresh window disappears entirely because nothing is copied.

The decision rule: incremental refresh solves "we reload unchanged history"; it does not solve "our queries are slow" or "we need live data". Diagnose which sentence describes you before configuring anything - misdiagnosis here is how teams add complexity and keep the symptom. A focused performance optimisation assessment makes exactly this distinction in its first week.

What Does Success Look Like Afterwards?

Refresh duration drops by an order of magnitude and stops growing with history. The five-year model that took ninety minutes refreshes in six; next year, with six years stored, it still refreshes in six - because the work now scales with the day's changes, not the archive's size. Capacity pressure falls in proportion, often deferring an upgrade that looked inevitable.

Lock the win in with two habits: a duration alert that flags drift (the early warning that folding broke or volumes shifted) and a quarterly check that the stored-history policy still matches what the business actually queries. Five years stored when reports touch two is memory rented for nostalgia.

How Do You Verify Incremental Refresh Is Actually Working?

Three checks, in increasing depth. The refresh history is the first signal: a working policy shows duration dropping from hours to minutes after the first full load. The partition view through XMLA - SQL Server Management Studio or Tabular Editor connected to the workspace - shows the real evidence: dated partitions with only the recent ones carrying fresh processing timestamps.

The third check is the one that catches silent failure: confirm query folding by watching the source. A folded incremental refresh sends the source a handful of date-bounded queries; a broken one drags the full table across the wire and filters it afterwards, which the refresh history will happily report as "succeeded - four hours". Success without folding is the failure mode that hides.

Stop Paying for the Archive

Most blown-out refresh windows are the same bill: the nightly cost of reloading years that have not changed since they were first loaded. Incremental refresh, correctly folded and operationally owned, cancels that bill - and turns refresh duration from a growth curve into a flat line.

Verify the folding, schedule the first big load, and put an alert on drift. The morning the refresh finishes before the kettle does, the architecture has paid for itself.