Incremental Refresh
Power BI · Advanced Features
Theory
Incremental refresh splits a large imported table into date-based partitions so that each refresh only reloads recent data instead of the full history. You create two Date/Time parameters named exactly RangeStart and RangeEnd, filter the table with them (OrderDate >= RangeStart and OrderDate < RangeEnd), and then define a policy on the table: for example archive 5 years of data and incrementally refresh the last 10 days. When published, the Service creates the partitions and, on each scheduled refresh, reloads only the last 10 days while older partitions stay untouched.
The filter should fold to the source so that each partition query asks the database only for its date range; with a non-folding source, every partition would read all the data. Optional settings include getting the latest data in real time with DirectQuery for the newest partition, refreshing only complete days, and detecting data changes using a last-updated column.
Why we use it
Refreshing years of history every night is slow and heavy on the source. Incremental refresh makes refresh faster, more reliable and cheaper, while keeping full history in the model.
When to use it
Use it for large, append-mostly fact tables with a reliable date column, such as Sales or event logs. It is unnecessary for small tables and dimensions. Make sure the source supports folding the date filter.
How it works
- Large fact table refreshes slowly
- Create RangeStart and RangeEnd parameters
- Filter the date column with them (must fold)
- Define the policy: archive period and refresh period
- Publish; the Service builds partitions
- Is this a scheduled refresh after the first? Yes: Reload only the recent partitions. No: Initial refresh loads all history.
- Fast refresh with full history
Syntax
Home → Manage Parameters → New: RangeStart, RangeEnd (type Date/Time)
Filter the date column: each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd
Model view / Data pane → right-click table → Incremental refresh
Archive data starting N years | Incrementally refresh data starting N days
Optional: Get the latest data in real time | Only refresh complete days | Detect data changesExample
let
Source = Sql.Database("sql-prod-01", "SalesDB"),
Sales = Source{[Schema = "dbo", Item = "Sales"]}[Data],
#"Filtered Rows" = Table.SelectRows(Sales,
each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)
in
#"Filtered Rows"
// Policy on Sales: archive 5 years, refresh the last 10 days.
// Use >= on one boundary and < on the other so no row lands in two partitions.Key points
- Needs Date/Time parameters named exactly RangeStart and RangeEnd
- Use >= on one boundary and < on the other to avoid duplicates
- Partitions are created in the Service after publishing
- The date filter should fold to the source
- Best for large fact tables, not dimensions