Mastering Incremental Refresh in Power BI
Reduce Power BI dataset refresh times by up to 90% with incremental refresh. Step-by-step guide for large datasets with millions of rows.
Incremental refresh is one of the most impactful performance optimizations for Power BI datasets with large volumes of data. Instead of refreshing an entire dataset every time, incremental refresh only processes new and changed data, reducing refresh times by up to 90% and cutting resource consumption dramatically.
How Incremental Refresh Works
Traditional dataset refresh replaces all data in every table during each refresh cycle. For a sales table with 50 million rows spanning five years, the gateway must extract, transfer, and load all 50 million rows even if only yesterday's 10,000 rows are new.
Incremental refresh solves this by partitioning your table into time-based segments. Only partitions within the "refresh" window get reprocessed. Historical partitions remain untouched, dramatically reducing the data volume processed during each refresh.
Step-by-Step Configuration
Step 1: Create RangeStart and RangeEnd Parameters
In Power Query Editor, create two DateTime parameters:
- RangeStart: Set to a sample date (e.g., 1/1/2024). Type must be DateTime
- RangeEnd: Set to a later date (e.g., 2/1/2024). Type must be DateTime
These parameters must be named exactly RangeStart and RangeEnd (case-sensitive). Power BI uses them to generate partition boundaries.
Step 2: Filter Your Source Query
Apply a filter to your date column using these parameters. In Power Query, filter the date column to be "is after or equal to RangeStart" AND "is before RangeEnd." This filter enables Power BI to generate the appropriate SQL WHERE clause for each partition.
The filter must use the native query folding capabilities of your data source. If query folding breaks, the entire table will be extracted for each partition, negating the performance benefits.
Step 3: Define the Refresh Policy
Right-click the table in the model view and select "Incremental refresh and real-time data." Configure:
- Archive data starting: How far back to keep historical data (e.g., 5 years). These partitions are never refreshed
- Incrementally refresh data starting: The rolling window of data to refresh each cycle (e.g., 30 days). Only these partitions are processed during refresh
- Detect data changes: Optionally specify a column (like LastModifiedDate) to only refresh partitions where data actually changed
- Only refresh complete days: Prevents partial-day data issues by waiting for a full day before including it
Step 4: Publish and Configure
Publish to a Premium or Pro workspace. The first refresh processes all historical data (this may take hours for large datasets). Subsequent refreshes only process the incremental window, completing in minutes.
Real-World Performance Impact
| Scenario | Full Refresh | Incremental Refresh | Improvement | |----------|-------------|-------------------|-------------| | 50M row sales table (5 years) | 45 minutes | 4 minutes | 91% faster | | 100M row IoT sensor data | 2 hours | 8 minutes | 93% faster | | 20M row financial transactions | 25 minutes | 3 minutes | 88% faster |
Advanced Configuration Options
Real-Time Data with DirectQuery: In Premium workspaces, combine incremental refresh with a DirectQuery partition for the most recent data. Historical data uses fast Import mode while the current partition queries the source directly for real-time results.
Custom Partition Granularity: By default, Power BI creates daily, monthly, quarterly, or yearly partitions based on your refresh window. XMLA endpoint access (Premium) allows custom partition management for fine-grained control.
Detect Data Changes: Configure a "watermark" column (like LastModifiedDate) so Power BI only refreshes partitions where data actually changed. This further reduces refresh times when only a few partitions have new data.
Query Folding Requirements
Incremental refresh depends on query folding - the ability for Power Query to push filter operations back to the data source as native SQL. If query folding breaks, the entire dataset is pulled through the gateway before filtering.
Sources with reliable query folding: SQL Server, Azure SQL, Synapse Analytics, Oracle, PostgreSQL, Snowflake. Sources where folding may not work: flat files, web APIs, Excel files, some ODBC connections.
Verify query folding by right-clicking a step in Power Query and checking if "View Native Query" is available. If greyed out, folding has broken at that step.
Troubleshooting Common Issues
- First refresh takes too long: Expected behavior - the initial load processes all historical data. Schedule during off-hours
- Refresh fails with timeout: Check gateway timeout settings. Large initial loads may exceed default timeouts
- Data not appearing for current day: Enable "Only refresh complete days" if your source updates throughout the day to avoid partial data
- Row counts do not match: Verify your RangeStart/RangeEnd filters include boundary conditions correctly (>= and <)
Related Resources
Frequently Asked Questions
Does incremental refresh require Power BI Premium?
Basic incremental refresh works with Power BI Pro. However, advanced features like real-time DirectQuery partitions, XMLA endpoint partition management, and detect-data-changes require Power BI Premium or Premium Per User (PPU) licensing.
How much can incremental refresh reduce my refresh times?
Most organizations see 80-95% reduction in refresh times. A dataset that takes 45 minutes for full refresh typically completes in 3-5 minutes with incremental refresh configured. The improvement depends on the ratio of new data to historical data and your partition granularity.
What happens if my data source does not support query folding?
Without query folding, Power BI must download the entire dataset through the gateway for each partition, which can actually make refreshes slower than without incremental refresh. Verify query folding works by checking "View Native Query" in Power Query before configuring incremental refresh. SQL-based sources typically support folding; flat files and APIs typically do not.