Power BI incremental refresh, explained

A full refresh reloads every row of every table, every time. On a small model that is fine. On a fact table with tens or hundreds of millions of rows, it is slow, it burns memory, and it puts you one network hiccup away from a failed refresh at 6am. Incremental refresh fixes this by reloading only the rows inside a recent window and leaving the settled history alone.

This article explains what incremental refresh is, how the RangeStart and RangeEnd parameters drive it, how partitioning and detect data changes work, what the first refresh costs you, and the gotchas that trip teams up. It also covers how the feature behaves on Premium, Fabric, and Direct Lake.

What it is and why it matters

Incremental refresh tells Power BI to treat a table as a set of time-based partitions rather than one block. You define a policy such as "keep five years of history, refresh the last ten days." On each scheduled refresh, Power BI reprocesses only the partitions inside that refresh window and keeps the older partitions exactly as they are.

Three things improve as a result. Refresh time drops, because a ten-day reload is a fraction of a five-year one. Memory pressure drops, because Power BI is not holding a second full copy of the table in memory while it swaps the old one out. And reliability improves, because a shorter refresh has fewer rows in flight and far less to lose if something fails midway. On capacity-based workloads, that shorter, lighter refresh also frees the capacity for other work.

How RangeStart and RangeEnd work

The whole feature hangs on two parameters with reserved names: RangeStart and RangeEnd. They must be of type date/time, and the names have to be exact. Power BI recognizes them and takes over their values at refresh time.

You create them in Power Query and then filter your fact table's date column between them:

// Two parameters, both Date/Time
RangeStart = #datetime(2024, 1, 1, 0, 0, 0)
RangeEnd   = #datetime(2024, 1, 2, 0, 0, 0)

// Filter the fact table's date column between them
Sales = Table.SelectRows(
    Source,
    each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
)

Note the boundary logic: greater-than-or-equal on the start, strictly-less-than on the end. That half-open range is what keeps a row from landing in two partitions at once. Get it wrong and you will double-count rows on partition boundaries.

The values you type are only placeholders for building the query in the desktop. Once you define the incremental refresh policy on the table and publish, the service ignores your placeholder values and substitutes its own RangeStart and RangeEnd for each partition it processes.

Partitioning and the refresh policy

When you configure the policy, you set two ranges. The archive range is how much total history to store, for example five years. The incremental range is how far back to actually refresh on each run, for example the last ten days.

Power BI uses these to split the table into partitions, typically by month or by day depending on the ranges. Partitions older than the incremental window are loaded once and then frozen. Partitions inside the window are reprocessed on every refresh. A row that was written yesterday gets picked up; a row from three years ago is never touched again unless you force a full reload.

The archive range can also roll forward over time, so the oldest partition drops off as a new one is added and the model stays at a stable size instead of growing without bound.

Detect data changes

Refreshing the last ten days every night still means reprocessing ten partitions even if only today changed. Detect data changes cuts that further. You point it at a column that reliably updates when a row changes, usually a "last modified" timestamp.

Before reloading a partition, Power BI checks the maximum value of that column against what it stored last time. If the max has not moved, the data in that partition has not changed, so Power BI skips it entirely. Done well, a nightly refresh might reprocess a single day's partition instead of ten. The catch is that your modified-date column has to be trustworthy: if a row can change without its timestamp advancing, detect data changes will skip a partition that actually needed reloading.

The first refresh is a full load

Here is the part that surprises people. Incremental refresh saves time on every refresh after the first. The first refresh in the service has to build the entire archive range from scratch, so it loads all five years at once. That initial load can run long and, on a large table, can hit the same limits you were trying to escape.

On Premium or Fabric capacity, the way around this is the XMLA endpoint. Tools like Tabular Editor or SQL Server Management Studio let you create and refresh partitions selectively, so you can bootstrap the history in controlled chunks rather than one large operation that risks timing out. Plan the first load deliberately; it is a one-time cost but a real one.

Gotchas worth knowing

Query folding is the big one. Incremental refresh works by pushing the RangeStart and RangeEnd filter down to the source as a native query. If the source folds, that becomes a WHERE clause and only the needed rows ever leave the database. If it does not fold, Power BI pulls the entire table and filters locally, which is slower than a plain full refresh. This is why incremental refresh pairs well with SQL Server, Azure SQL, and Synapse, and badly with flat files, Excel, and most web APIs. Right-click the last applied step in Power Query and check whether "View Native Query" is available; if it is greyed out, folding has broken and you need to fix the query before folding, not after.

The date column matters too. The filter has to sit on a real date/time column that exists in the source, not on a column you compute in Power Query after folding stops. Filtering on a derived column pushes the transformation past the fold and quietly turns your incremental refresh into a full scan.

A few smaller ones. RangeStart and RangeEnd must be date/time even if your key is a date-only or integer surrogate, so you may need a small conversion that still folds. And test the boundary logic on real data, because off-by-one errors on the half-open range are easy to miss until totals drift.

How it fits with Premium, Fabric, and Direct Lake

On Pro, incremental refresh works, but you lose the XMLA endpoint and the fine partition control that makes large initial loads manageable. On Premium and Fabric capacities you get that control, plus longer refresh limits and the headroom to run bigger models comfortably. If you are working through the shift from Premium to Fabric capacities, incremental refresh policies carry over, since the same Power BI workload runs on both. Our write-up on the move from Power BI Premium to Fabric covers what changes there.

Direct Lake is a different story. It has no import copy to refresh, so incremental refresh in the Import sense does not apply. Freshness comes from the pipelines that write Delta tables into OneLake, and any incremental logic lives in that data engineering layer instead of the semantic model. If you are weighing storage modes, Import vs DirectQuery vs Direct Lake lays out the trade-offs, and Direct Lake explained goes deeper on how it reads from OneLake.

Whatever mode you land on, incremental refresh is most effective when the model underneath it is clean: a proper star schema, a real date table, and measures that fold. Our notes on semantic model best practices go into that groundwork.

Getting it right the first time

Incremental refresh is one of the most valuable settings in Power BI, and also one of the easiest to configure in a way that silently does nothing. The failure mode is quiet: the policy looks configured, refreshes still succeed, and folding has broken so the whole table reloads anyway. If your large models take longer to refresh than they should, or you want incremental refresh set up correctly against a source that actually folds, get in touch and we will help you scope it.