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

  1. Large fact table refreshes slowly
  2. Create RangeStart and RangeEnd parameters
  3. Filter the date column with them (must fold)
  4. Define the policy: archive period and refresh period
  5. Publish; the Service builds partitions
  6. Is this a scheduled refresh after the first? Yes: Reload only the recent partitions. No: Initial refresh loads all history.
  7. 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 changes

Example

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

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic