Power BI incremental refresh explained: how to handle large datasets efficiently
If your Power BI reports are slow to refresh, or if your dataset has grown to the point where a full refresh takes hours, incremental refresh is likely the solution you need. In this guide, we explain exactly how it works, how to set it up correctly, and what to watch out for when working with large datasets.
Why full refresh becomes a problem at scale
Every Power BI dataset has a refresh cycle. By default, Power BI performs a full refresh: it clears all existing data and reloads everything from the source. For small datasets, this is perfectly fine. For large ones, it creates a cascade of problems.
A full refresh of a dataset containing several years of transaction data can take hours. During that time, users cannot access up-to-date information. The refresh window consumes significant compute resources. If the refresh fails partway through, the entire process starts over. And as your data grows month by month, the problem compounds.
Incremental refresh solves this by only loading new or changed data, rather than reprocessing everything from scratch. Instead of reloading five years of history every night, you load only the records that have been added or modified since the last refresh. The historical data stays in place, untouched and already loaded.
The result is dramatically faster refresh times, lower resource consumption and more reliable refresh cycles, even as your data grows.
How incremental refresh works technically
Power BI’s incremental refresh is built on the concept of partitioning. Rather than storing your dataset as a single monolithic block, Power BI divides it into time-based partitions, typically by day, month or year. Each partition contains a subset of the data corresponding to a specific time window.
When a refresh runs, Power BI evaluates which partitions need to be updated based on the rules you define. Historical partitions, covering data older than your defined threshold, are left untouched. Only the recent partitions, covering data within the refresh window, are reloaded. New partitions are created for data that has arrived since the last refresh.
This partitioning happens automatically in the background. From the report user’s perspective, the dataset behaves exactly as it always did. The partition structure is an implementation detail managed by Power BI.
RangeStart and RangeEnd parameters
Incremental refresh relies on two mandatory Power Query parameters: RangeStart and RangeEnd. These are DateTime parameters that Power BI uses to communicate the time window to your data source query.
When you define these parameters in Power Query and use them to filter your date column, Power BI can fold the filter into the query sent to your data source. This means your database receives a query with a specific date range, rather than being asked to return all historical data. The result is a much smaller data transfer between source and Power BI.
The parameter names must be exactly RangeStart and RangeEnd, including capitalisation, for Power BI to recognise them as incremental refresh parameters.
Store rows in the last and detect data changes
In addition to the standard incremental refresh window, Power BI offers two advanced options worth understanding.
The first is the ability to store rows from the last N periods permanently. This is useful when you need to keep several years of historical data in your dataset but only want to refresh the most recent months. Historical partitions are loaded once and never refreshed again, saving significant processing time.
The second option, detect data changes, allows you to specify a column that Power BI uses to determine whether a historical partition needs refreshing. If the maximum value of that column has not changed since the last refresh, the partition is skipped entirely. This is particularly useful for datasets where historical records occasionally get updated, such as order data where status fields change after initial creation.
Setting up incremental refresh: a step-by-step guide
Step 1: Define RangeStart and RangeEnd in Power Query
Open your report in Power Query Editor. Navigate to Manage Parameters and create two new parameters with the following specifications.
For RangeStart: name it exactly RangeStart, set the type to Date/Time, and give it a sample value representing the start of a recent period, for example the first day of the current month.
For RangeEnd: name it exactly RangeEnd, set the type to Date/Time, and give it a sample value representing the end of that same period, for example the last day of the current month.
Step 2: Apply the parameters to your date filter
In your data query, apply a filter on your date column using the RangeStart and RangeEnd parameters. The filter should include rows where the date is greater than or equal to RangeStart and strictly less than RangeEnd.
It is important that Power BI can fold this filter down to the data source. This means your data source must support query folding, and the filter must be applied before any steps that break folding, such as certain merge or custom column operations. If folding is not possible, incremental refresh will still work, but the full dataset will be transferred from the source on every refresh, which undermines the performance benefit.
To verify that query folding is active, right-click on the filter step in Power Query and check whether the “View Native Query” option is available. If it is, folding is working correctly.
Step 3: Configure the incremental refresh policy
Close Power Query and return to Power BI Desktop. Right-click on your table in the Fields pane and select “Incremental refresh and real-time data”.
In the incremental refresh configuration panel, you will define two time windows. The first is the archive window: how many years or months of historical data you want to store in the dataset. The second is the refresh window: how many days or months of recent data should be refreshed on each cycle.
A typical configuration for a transactional dataset might be to store five years of data and refresh the last three months. This means Power BI keeps five years of history, but only reprocesses the last three months on each refresh, regardless of how much of that history actually changed.
Step 4: Publish and verify
Once you have configured the policy, publish the report to the Power BI Service. The first refresh after publication will be a full refresh, loading the entire archive window from your data source. This initial load can take significant time depending on data volume, but subsequent refreshes will only process the defined refresh window.
After the first refresh completes, you can inspect the partition structure using tools such as SQL Server Management Studio connected to the XMLA endpoint, or using Tabular Editor. You will see the dataset divided into time-based partitions, with historical partitions marked as processed and the recent partitions refreshed on each cycle.
Common pitfalls and how to avoid them
Query folding breaks silently
One of the most common issues with incremental refresh is query folding breaking without an obvious error. If a transformation step in Power Query breaks folding, Power BI will silently fall back to loading all data before applying the filter in memory. Your refresh will still work, but it will be a full data transfer every time, negating the incremental benefit.
Always verify query folding is active on your filter step, and be careful about the order of transformations. Steps that commonly break folding include merging queries from different sources, adding custom columns using M functions that cannot be translated to SQL, and certain type conversion operations.
Timezone handling with RangeStart and RangeEnd
Power BI uses UTC internally for the RangeStart and RangeEnd values passed to your data source. If your source data stores timestamps in a local timezone, this can cause data to be missed or duplicated at partition boundaries. A common mitigation is to add a buffer to your refresh window, refreshing slightly more data than strictly necessary to account for timezone offsets, and ensuring your semantic model converts timestamps to UTC consistently.
DirectQuery and incremental refresh
Incremental refresh is primarily designed for Import mode datasets. It can be combined with DirectQuery through the hybrid tables feature introduced in recent versions of Power BI, where historical partitions are stored in Import mode and the most recent partition queries the source in real time. This combination gives you fast historical queries with real-time freshness for current data, but it requires a Premium or Fabric capacity and careful configuration.
Dataset size limits
Incremental refresh expands what is practically possible within Power BI’s dataset size limits, but it does not remove them entirely. The compressed in-memory size of your full archive window must still fit within the limits of your capacity. For very large datasets, Direct Lake mode in Microsoft Fabric may be a more appropriate architecture, as it reads from Delta tables in OneLake without importing data into memory at all.
When incremental refresh is the right choice
Incremental refresh is the right solution when your dataset is large enough that full refreshes are slow or unreliable, your data has a clear time dimension that can be used as the partition key, your data source supports query folding, and the majority of your data is historical and does not change after initial load.
It is less appropriate for datasets where records are frequently updated across the full history, where the data source does not support query folding, or where the dataset is small enough that a full refresh completes in a few minutes without issue.
Conclusion
Incremental refresh is one of the most powerful tools available to Power BI developers working with large datasets. When configured correctly, it reduces refresh times from hours to minutes, improves reliability and makes it practical to maintain years of historical data in a single semantic model.
The key is getting the fundamentals right: valid RangeStart and RangeEnd parameters, confirmed query folding, and a sensibly configured archive and refresh window. Done well, incremental refresh becomes invisible to your end users. They simply get faster, more reliable access to current data.
Webdashboard helps organisations share Power BI reports and datasets securely with anyone, with full support for large, enterprise-grade semantic models. Learn more at webdashboard.com.