Power BI Incremental Refresh
A full refresh reloads every row each time. Large tables make this slow and wasteful, since old records rarely change. Incremental refresh reloads only the recent slice and keeps older data untouched.
The Diary Analogy
Imagine a diary with five years of entries. Updating it each evening requires writing only today's page. Rewriting the full diary every night would waste hours. Incremental refresh follows the same sense: add the new page and leave the old pages alone.
Full refresh: [########## 5 years ##########] reload everything
Incremental refresh: [ archive: 4 years 11 months ][ refresh window ]
kept as is reloaded each time
Requirements
- A date or date and time column in the table to partition by.
- A data source that supports filtering on that column, ideally with query folding.
- A published model in a workspace that supports the feature.
Step 1: Create RangeStart and RangeEnd Parameters
Power BI uses two reserved parameter names. Spelling and data type must match exactly.
- Open Power Query Editor and select Manage Parameters, then New Parameter.
- Name the first parameter RangeStart, set the type to Date/Time, and enter any sample start date.
- Create a second parameter named RangeEnd with type Date/Time and a sample end date.
The sample values only serve Desktop. The Service replaces them with real values for each partition.
Step 2: Filter the Table
Filter the date column so that it is on or after RangeStart and before RangeEnd. Use custom filter logic:
= Table.SelectRows(Source, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)Apply the greater-than-or-equal test on one boundary only. This pattern prevents the same row from landing in two partitions.
Step 3: Define the Policy
- Close Power Query and load the data.
- Right-click the table in the Data pane and choose Incremental refresh.
- Switch on Incremental refresh and set how many years of history to store, such as 5 years.
- Set the refresh window, such as 7 days.
- Optionally enable Only refresh complete days.
- Select Apply.
What Happens in the Service
After publishing, the first refresh creates partitions: one for each past period and one for the recent window. Later refreshes touch only the recent partitions. Older partitions stay frozen and out of the refresh work.
Partitions after publish 2022 | 2023 | 2024 | 2025 | Jan-Sep 2026 | Last 7 days frozen frozen frozen frozen frozen REFRESHED nightly
Optional Settings
| Setting | Purpose |
|---|---|
| Detect data changes | Refresh a partition only when a chosen column shows a newer value |
| Only refresh complete days | Avoids partial-day numbers |
| Real-time DirectQuery partition | Adds the newest data live on top of the imported history |
Worked Example: A Telecom Call Log
A telecom firm stores two billion call records over three years. A full refresh takes six hours and often fails. The analyst sets history to three years and the refresh window to three days. Nightly refresh now processes only recent calls and finishes in twelve minutes. Readers receive fresh numbers each morning, and the source database stays calm.
Tips
- Keep the partition column free of nulls.
- Check that the source supports query folding for the date filter.
- Avoid downloading the published file back to Desktop, since republishing would rebuild partitions.
- Match the refresh window to how far back source records may change.
Key Points
- Incremental refresh reloads only recent data and keeps history frozen.
- Reserved parameters named RangeStart and RangeEnd drive the partitions.
- A policy sets the history length and the refresh window.
- Partitions form automatically after publishing.
- Query folding keeps refresh fast.