Power BI / Advanced: performance and governed delivery / Lesson 17

Incremental refresh and partition contracts

Define non-overlapping time windows, historical retention and late-change behavior before relying on an incremental refresh policy.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Explain RangeStart and RangeEnd parameter assumptions.
Validate exclusive/inclusive boundaries with tiny fixtures.
Separate policy definition from actual service partition processing.

Define archive and refresh windows

Incremental refresh can maintain historical data while refreshing a more recent window under a supported policy. Storing history and reprocessing recent changes are different decisions. A sale corrected far outside the refresh window may remain unchanged in the model unless a planned backfill or other supported process covers it. Define retention, late-arrival behavior, corrections and deletions in the data contract. Do not choose windows from a universal tuning number. The appropriate policy follows business change patterns, source capabilities and recovery needs, including how a failed initial or subsequent processing operation will be investigated.

Filter with precise parameters

The common setup uses case-sensitive Date/Time parameters named RangeStart and RangeEnd and filters the intended date/time column. The first M expression is a local demonstration using an inclusive start and exclusive end. Only the Jan1 row belongs to this one-day window. In a real policy the service supplies partition ranges; hard-coded sample values are not evidence that partition automation ran. Match column and parameter types and preserve the intended time-zone convention. Including both ends can duplicate a boundary row across adjacent windows, while excluding both can lose it.

powerquery Unexecuted half-open window expression

let
    RangeStart = #datetime(2026, 1, 1, 0, 0, 0),
    RangeEnd = #datetime(2026, 1, 2, 0, 0, 0),
    Source = #table(type table [When = datetime, Amount = number], {{#datetime(2026, 1, 1, 0, 0, 0), 40}, {#datetime(2026, 1, 2, 0, 0, 0), 30}}),
    Window = Table.SelectRows(Source, each [When] >= RangeStart and [When] < RangeEnd)
in
    Window

The intended output contains Jan1/40 only. These local bindings demonstrate boundary logic and do not create real model parameters or service partitions.

Verify source work and first processing

A policy that looks incremental can still perform excessive source work if the date filter is not applied efficiently through the supported connector. Review query folding and documented source requirements rather than assuming parameter names guarantee pushdown. The first service processing can need to establish the retained history and may be much larger than a later recent-window refresh. Plan capacity and recovery accordingly without running a load test. Feature availability, optional real-time partitions and advanced management capabilities depend on the documented license/capacity and model configuration; recheck current rules instead of treating every option as part of basic Desktop authoring.

Test adjacent windows

The second program assigns two synthetic timestamps to adjacent half-open windows. Each row appears once, and the union covers the intended dates without overlap. It is not a partition engine and uses only two public values. Add an exact-boundary case, a late correction and a missing date to the review matrix. Check totals across the full retained range as well as the recently refreshed subset. A successful small preview proves neither historical completeness nor the service’s actual partition policy, so keep expected arithmetic distinct from operational processing evidence.

python Synthetic adjacent-window oracle

from datetime import datetime
rows = [datetime(2026, 1, 1), datetime(2026, 1, 2)]
a, b, c = datetime(2026, 1, 1), datetime(2026, 1, 2), datetime(2026, 1, 3)
first = [row for row in rows if a <= row < b]
second = [row for row in rows if b <= row < c]
print(len(first), len(second))
print(len(set(first) & set(second)))

Expected output

1 1
0

Each window has one row and the intersection has zero. No source, partition or refresh operation is accessed.

Record the policy and its limits

Record parameter types, filtered column, boundary convention, retention window, refresh window, correction policy, folding evidence and service processing outcomes. Protect recovery copies and compatibility plans before replacing a published model, because redeployment can affect retained partitions and downstream expectations. This course performs no publication or refresh and measures no capacity. The M fragment is non-runnable and Python tests only window membership. A useful handoff identifies which historical changes require explicit backfill and who owns that process, instead of claiming that incremental refresh automatically discovers and repairs every old correction or deletion.

Common errors and fixes

Adjacent partitions both include their shared end timestamp

Why it happens: Both range comparisons were inclusive.
How to fix it: Use the documented one-sided equality convention and verify exact boundaries.
Prevent it next time: Keep a timestamp exactly at RangeEnd in the fixture.

An old correction never appears after recent-window refresh

Why it happens: The changed row falls outside the reprocessed range.
How to fix it: Apply the approved historical correction/backfill process.
Prevent it next time: Define late-change behavior with business owners.

Parameter names are treated as proof of source-side filtering

Why it happens: Folding and connector behavior were never verified.
How to fix it: Review supported query pushdown evidence separately from local results.
Prevent it next time: Record source capabilities and first-processing requirements.

Check your understanding

1. Why should one adjacent window use<end instead of<=end when the next starts at that same instant?

Show worked solution

With an inclusive next start, exclusive prior end assigns the boundary row once. Including both would duplicate it and inflate totals. The convention must match the actual parameter and column types. Test the exact instant rather than only midday rows that cannot expose an overlap bug.

2. Does refreshing the latest month guarantee correction of a transaction from last year?