Power BI Incremental Refresh, Step by Step: Setup Guide + 8 Lessons From a Real P&L Model

What incremental refresh is — and when to use it

What changed

• Microsoft's definition: incremental refresh "extends scheduled refresh operations by providing automated partition creation and management for semantic model tables that frequently load new and updated data."
• In practice the table is split into date partitions. Old periods are archived (loaded once, then kept); only the recent refresh window is re-read on every scheduled refresh.
• Benefits Microsoft lists: faster and more reliable refreshes, lower memory and source load, and models that can grow to billions of rows without a full reload each time.
• Use it when: a large fact table (millions of rows) keeps receiving new or changed rows, it has a reliable date or date/time column (e.g. Posting Date), the source can filter by that date with query folding (SQL databases, Snowflake, Databricks…), and full refreshes are slow or close to the limit (2 hours on Pro, 5 hours on Premium capacity).
• Available on Power BI Pro, Premium per user, Premium/Fabric capacity and Embedded. Real-time data with DirectQuery needs Premium, PPU or Embedded.
• Skip it when: the table is small and a full refresh takes seconds; there is no trustworthy date column; old rows change often far outside any sensible refresh window; or the source can't fold the date filter (Power BI would read every row and then filter, which defeats the purpose).

Why it matters

Incremental refresh is a trade: much faster refreshes in exchange for rules about what gets re-read. Decide it consciously, before the first publish.

How to set up incremental refresh in Power BI

What changed

Lesson 1 – The filter must be a closed–open window on reserved parameter names

What changed

• Parameters must be named exactly RangeStart and RangeEnd (reserved, case-sensitive).
• Use >= RangeStart and < RangeEnd. Equals, "or", or <= on both ends drops boundary rows or loads them into two partitions.

Before

Table.SelectRows(#"Appended Query", each [Posting Date] = #"Start Range" or [Posting Date] >= #"End Range")

After

Table.SelectRows(Source, each [Posting Date] >= RangeStart and [Posting Date] < RangeEnd)

Why it matters

The service rewrites both parameters per partition; only a closed–open window puts every row in exactly one partition.

Lesson 2 – Match the column to the Date/Time parameters, right after the source

What changed

• RangeStart/RangeEnd must be Date/Time; the filtered column should be Date/Time (or an integer yyyymmdd key).
• A Date column against Date/Time parameters fails with: Expression.Error: We cannot apply operator < to types DateTime and Date.
• Change the type in the step right after the source so it still folds; don't wrap the column in a function inside the filter.

Before

Table.TransformColumnTypes(Source, {{"Posting Date", type date}})

After

Table.TransformColumnTypes(Source, {{"Posting Date", type datetime}})

Why it matters

The time is always 00:00, so relationships to a Date calendar still match; format it as a short date in the model.

Lesson 3 – Filter before the append, and filter every appended table

What changed

• A filter after appending manual tables (Excel, entered data) can't fold, so every partition pulls the whole fact table.
• Filter the database query right after source + type step, where it becomes a WHERE clause.
• Apply the same filter to each manual table, or its rows are added to every partition and appear duplicated.
• Rows with an empty date fall outside every partition and disappear.
• Check with View Native Query on the filter step. Microsoft recommends all partitions query a single source, so consider loading larger manual inputs as their own table.

Before

Appended = Table.Combine({Fact, ManualInput}), then filter Appended

After

filter Fact and ManualInput separately with the same RangeStart/RangeEnd condition, then Table.Combine

Why it matters

The "Unable to confirm if the M query can be folded" dialog warning is expected with a final append; confirm the database step folds before publishing.

Lesson 4 – Archive in years, refresh in months, never touch RangeStart/RangeEnd again

What changed

• Archive N years: year partitions start on 1 January, not the fiscal-year start; add a year if you need complete early fiscal years.
• Incrementally refresh N months: only this window reloads; older months freeze.
• Both windows roll with each refresh date. "Imported 1/1/2021–7/31/2026" plus "refreshed 8/1/2026–10/31/2026" means the model holds 2021 to the current month.
• Desktop parameter values only size your development load; the service overrides them.

Before

Update RangeStart/RangeEnd in Desktop every month.

After

Keep a small dev window; let the policy (e.g. 5 years / 3 months) roll in the service.

Why it matters

The dialog dates are calculated from each refresh date, not fixed cut-offs.

Lesson 5 – Size the refresh window for late postings and year-end

What changed

• Partition by posting date. An entry made 7 Oct with posting date 30 Sep is captured while September is inside the window.
• With 3 months, a 30 Sep posting is captured on October and November refreshes, missed in December.
• Year-end adjustments are usually posted on the fiscal year's last day even when entered weeks later; if they can arrive more than ~2 months late, use 6 months.
• Keep "Only refresh complete periods" off to include today's postings.
• Keep "Detect data changes" off unless you have a real last-modified column; Microsoft says it shouldn't be the partition column. With posting date, a correction inside a month doesn't change its max date, so it's skipped.

Before

3 months, Detect data changes on Posting Date.

After

3 months (6 if closing entries arrive late), Detect data changes off.

Why it matters

The refresh window is a business rule: it must cover the longest gap between posting date and when the entry reaches the source.

Lesson 6 – Set the refresh time zone

What changed

• The service uses UTC for the current date unless a time zone is set in the model's Refresh settings.

Before

01:00 local refresh left on UTC still "thinks" it's the previous day.

After

Set your zone (e.g. Europe/Rome).

Why it matters

Otherwise month windows can roll a day late.

Lesson 7 – Republishing from Desktop resets the partitions

What changed

• After the first refresh you can't download the .pbix; keep the PBIP project in source control as master.
• Republishing from Desktop removes existing partitions and data; the next refresh is a full reload.
• On Premium/PPU/Fabric, deploy metadata-only via XMLA (ALM Toolkit, Tabular Editor).
• Use a thin report so report changes never touch the model.

Before

Fix a measure, republish the model, wait for a multi-year reload.

After

Metadata-only deploy, or off-hours full reload; publish report changes as a thin report.

Why it matters

Plan how you'll ship changes before the first publish.

Lesson 8 – Changed the filter or the history range? Republish and reload — the refresh window won't catch it

What changed

• Example: the source query first kept only FY2025 onwards. Later you remove that filter so earlier fiscal years are included too. The policy archives 5 years and refreshes the last 3 months.
• A normal scheduled refresh only re-reads the last 3 months. The archived partitions are never queried again, so the earlier fiscal years stay missing — even though the query now allows them. Microsoft: "Rows with a date/time older than the refresh period are no longer refreshed."
• Also check the archive period: years older than "Archive data starting … before refresh date" are not kept in the model at all. Widen it first if the new years fall outside it.
• On Power BI Pro: change the query (and policy) in Desktop, publish again, then refresh off-hours. Republishing replaces the model; Microsoft: it "forces a full refresh on the model when a subsequent refresh operation is performed", so every archived year is reloaded with the new filter. Make sure the full reload fits in the 2-hour limit.
• On Premium, PPU or Fabric with the XMLA endpoint: deploy only the metadata (Tabular Editor or ALM Toolkit, keeping partitions), then refresh just the affected year partitions in SSMS, or with TMSL and applyRefreshPolicy: false, during off-hours — no multi-year reload of everything.
• Same rule for other history-wide changes: a new column in the table, a changed data type or join, or corrections to rows older than the refresh window. Anything that changes rows outside the window needs a full reload or a targeted partition refresh.

Before

Widen the filter in Desktop → wait for the next scheduled refresh → older fiscal years are still missing.

After

Widen the archive period if needed → publish again (Pro) or metadata-only deploy (XMLA) → full reload, or refresh the affected year partitions → check row counts per fiscal year.

Why it matters

Incremental refresh only fetches what is inside the refresh window. Whenever you change what the history should contain, you have to reload that history yourself — otherwise the report silently shows the old years.