- Get link
- X
- Other Apps
This article explains how to design and implement robust incremental refresh patterns when working with Excel, Power Query, and Power BI so that large datasets refresh quickly while keeping reports stable and maintainable.
1. What incremental refresh really means in Excel and Power BI
Incremental refresh is a pattern where only new or changed data is loaded during refresh instead of reloading the entire history every time. In the Microsoft data stack this concept is first-class in Power BI, where the semantic model is automatically partitioned by date ranges and only recent partitions are refreshed. In Excel, however, Power Query does not have a native incremental refresh feature with automatic partitions, so you must combine Excel with Power BI or implement manual incremental patterns in Power Query.
In Power BI Desktop, incremental refresh is configured by defining two special Power Query parameters, RangeStart and RangeEnd, using them to filter a Date or DateTime column, and then defining an incremental refresh policy on the table. After the model is published, the Power BI service creates partitions and refreshes only the partitions that fall into the incremental window you configured.
By contrast, Power Query in Excel can apply filters and parameters, but it does not create partitions or apply server-side incremental policies. Community guidance is consistent that Excel Power Query on its own does not support the same partitioned incremental refresh that exists in Power BI.
Note : When someone says “incremental refresh in Excel,” they usually mean one of two things: either Excel is connected to a Power BI dataset that uses incremental refresh, or Power Query in Excel is using a custom pattern that loads only new rows but still stores the combined result in a single table.
2. Architecture options for incremental refresh with Excel
Before choosing a pattern, it is important to decide how Excel and Power BI will work together in your solution. The following architectures cover most real projects.
2.1 Excel as a front-end on top of a Power BI dataset
In this architecture, incremental refresh is configured in a Power BI dataset, and Excel connects to that dataset using “Analyze in Excel” or “Get Data > From Power BI Dataset.” All heavy lifting is done by the Power BI semantic model, while Excel is used for pivot tables, formulas, and ad-hoc analysis.
- Power BI Desktop connects to the raw data sources and defines queries in Power Query.
- An incremental refresh policy is applied to the large fact table using
RangeStartandRangeEnd. - After publishing and running the first refresh, the dataset is partitioned and maintained in the service.
- Excel workbooks connect to the published dataset and refresh quickly because they only query the already-partitioned model.
This pattern is ideal when you need enterprise-grade refresh performance and governance but still want Excel as the primary consumption tool for analysts and business users.
2.2 Excel-only pattern: manual incremental load in Power Query
When Power BI is not available, you can still simulate incremental refresh in Excel by designing your queries so that only new data is pulled from the source and then appended to an existing historical table stored in the workbook. This approach does not create server partitions, but it can significantly reduce extraction time from databases or APIs.
The key ideas are:
- Keep a “History” table in Excel that stores all previously loaded rows.
- Determine a “watermark” date such as the maximum transaction date in the History table.
- Use Power Query to request only rows newer than that watermark from the source.
- Append the new rows to the History table to produce a combined table.
This pattern still requires the combined table to be processed by Power Query during refresh, but it avoids repeatedly pulling the entire history from remote systems.
2.3 Incremental refresh in dataflows feeding Excel and Power BI
Another architecture is to implement incremental refresh in a Power BI dataflow or Fabric equivalent and let both Power BI datasets and Excel workbooks consume that dataflow. In this case, incremental refresh happens in the dataflow entity, and Excel simply imports the already-managed history.
It is important to be aware of capacity limitations. Official guidance notes that incremental refresh for dataflows is not available in shared capacities and is supported only in premium capacities, along with other limitations related to refresh timeouts and computed tables.
Note : If you need many downstream reports and workbooks with consistent, governed history, a dataflow or lakehouse with incremental refresh is often a more scalable pattern than implementing separate manual patterns in individual Excel files.
3. Core pattern 1: RangeStart/RangeEnd incremental refresh with Power BI
This pattern is the standard way to implement incremental refresh for large tables and is the best option when Excel is used as a front-end on top of Power BI.
3.1 Preparing the fact table
Start by identifying a suitable column in your fact table that represents the time grain for partitioning. This is usually a transaction date, posting date, or load timestamp. The column must be of type Date or DateTime to work with incremental refresh policies.
3.2 Defining RangeStart and RangeEnd parameters
In Power BI Desktop, open Power Query Editor and create two parameters:
RangeStart– type DateTime, current value set to a recent past date.RangeEnd– type DateTime, current value set to a slightly later date thanRangeStart.
These parameters must have the exact, case-sensitive names RangeStart and RangeEnd because the incremental refresh engine searches for them by name.
3.3 Filtering the table by parameters and ensuring query folding
Apply a filter to the fact table so that only rows between RangeStart and RangeEnd are loaded into the model. This filter should be expressed in a way that preserves query folding so that the filter is pushed down to the source system, especially when working with large relational databases.
Incremental refresh works best for structured, relational sources such as SQL Database or Azure Synapse, where query folding is robust and date filters can be translated to efficient server-side queries.
// Example M filter in Power Query let Source = Sql.Database("myServer", "SalesDW"), FactSales = Source{[Schema="dbo", Item="FactSales"]}[Data], FilteredRows = Table.SelectRows( FactSales, each [OrderDateTime] >= RangeStart and [OrderDateTime] < RangeEnd ) in FilteredRows 3.4 Defining the incremental refresh policy
After applying the parameter-based filter, switch back to the model view in Power BI Desktop. Right-click the table and choose the incremental refresh configuration dialog. In this dialog, you typically specify:
- The total historical period to keep, such as “store rows for the last 5 years.”
- The incremental refresh window, such as “refresh rows in the last 7 days.”
- Optional settings like getting real-time data with DirectQuery for the latest partition.
Once this policy is defined and the model is published, the Power BI service creates partitions according to the policy and refreshes only the most recent partitions, dramatically reducing refresh times and resource usage for growing datasets.
3.5 Example configuration and policy table
Consider a transaction table with five years of sales data. A typical policy might keep five years of history but only refresh the last seven days on each scheduled refresh. The following table summarizes common policies.
| Scenario | Stored history | Incremental window | Partition grain |
|---|---|---|---|
| Daily sales report | 5 years | Last 7 days | Day |
| Financial ledger | 10 years | Last 30 days | Month |
| Web analytics | 2 years | Last 3 days | Day |
| Large IoT dataset | 1 year | Last 24 hours | Hour or day |
3.6 Behavior of RangeStart and RangeEnd after publishing
In Power BI Desktop, the values of RangeStart and RangeEnd only control how much sample data is loaded for development. After the model is published and incremental refresh is enabled in the service, the engine automatically overrides these parameter values to cover the full historical period and incremental window defined in the policy. This means you usually set a small range in Desktop to keep development models compact and let the service manage the actual partitions.
4. Core pattern 2: Excel-driven incremental load in Power Query
When you cannot use Power BI incremental refresh, you can still implement a practical pattern in Excel Power Query that loads only new rows from a source and appends them to an existing historical table stored in the workbook.
4.1 Designing the History and Config tables
Create the following structures in Excel:
- History table – an Excel table (for example,
tblHistory) that stores all previously loaded rows. - Config table – a small one-row Excel table (for example,
tblConfig) with a single columnLastLoadDate, storing the last successfully processed date.
Initially, tblHistory may be empty and LastLoadDate may be set to a very old date. After each successful refresh, you manually or programmatically update LastLoadDate to the maximum date present in tblHistory.
4.2 Creating a watermark parameter from Excel
In Power Query, create a query called LastLoadDate that reads the value from tblConfig using Excel.CurrentWorkbook(). Then convert that scalar to a Date or DateTime and reuse it in the main extraction query.
// Query: LastLoadDate let Source = Excel.CurrentWorkbook(){[Name="tblConfig"]}[Content], ChangedType = Table.TransformColumnTypes(Source,{{"LastLoadDate", type datetime}}), ValueOnly = ChangedType{0}[LastLoadDate] in ValueOnly 4.3 Loading only new rows from the source
Create a new query named NewData that reads from the operational source (SQL, API, CSV folder, etc.) and filters rows where the transaction date is greater than LastLoadDate. A simplified SQL-based example looks like this.
// Query: NewData let Source = Sql.Database("myServer", "SalesODS"), SourceTable = Source{[Schema="dbo", Item="SalesTransactions"]}[Data], Filtered = Table.SelectRows( SourceTable, each [OrderDateTime] > LastLoadDate ) in Filtered Now define another query named AllData that appends the existing history with the new rows.
// Query: AllData let History = Excel.CurrentWorkbook(){[Name="tblHistory"]}[Content], NewRows = NewData, Combined = Table.Combine({History, NewRows}) in Combined Load AllData to a worksheet table that overwrites tblHistory. After each successful refresh cycle, update LastLoadDate in tblConfig to the maximum date in AllData.
Note : This pattern reduces the volume of data extracted from the source system but does not prevent Power Query from processing the full combined table inside Excel. For very large datasets you still need Power BI incremental refresh or a centralized data platform.
4.4 Handling corrections and late-arriving data
Real-world sources often update historical rows, so a strict “only greater than last date” filter may miss corrections. A safer pattern is to pull a small overlap window, such as the last seven days, and re-deduplicate data on keys. In this pattern, your filter is not “greater than watermark,” but “greater than watermark minus a small buffer.” You then remove duplicates based on a primary key to keep the final table consistent.
5. Incremental refresh patterns for common scenarios
Incremental refresh patterns become most powerful when you tailor them to specific business problems. The following scenarios illustrate how to choose appropriate parameters and architectures.
5.1 Daily transactional reporting from large databases
For large transactional systems (for example, sales or ERP) with millions of rows per year, the recommended pattern is:
- Use Power BI incremental refresh on the main fact table with a Date or DateTime column.
- Keep several years of history (for example, five years).
- Refresh the last few days or weeks (for example, seven days) to capture new and updated transactions.
- Expose the model to Excel via a Power BI dataset connection.
This pattern ensures that refresh operations are predictable and short while Excel remains highly responsive for slicing and dicing.
5.2 Slowly changing or frequently corrected historical data
Some sources allow changes to older records long after they were originally created. For tables where corrections can occur months or years in the past, a pure incremental pattern may not be safe. In that case, combine incremental and periodic full refreshes.
- Implement incremental refresh with a suitable history and incremental window.
- Schedule occasional full refreshes (for example, weekly or monthly) to capture rare corrections in older partitions.
Expert guidance emphasizes that a full refresh is still recommended when the data source regularly modifies historical values or truncates and reloads tables.
5.3 API and web sources with rate limits
For API-based data, such as marketing or social media platforms, rate limits or paging constraints often make full refreshes expensive. In Excel-only solutions, the manual incremental pattern with a watermark parameter is particularly useful here because it minimizes the number of API calls and reduces the risk of hitting rate limits.
5.4 Shared dataflows for multiple workspaces and workbooks
When many reports and workbooks need the same historical data, move incremental logic into a dataflow or Fabric equivalent. Configure incremental refresh for the dataflow entity, then:
- Connect Power BI datasets to the dataflow for BI reporting.
- Connect Excel workbooks to the same dataflow for ad-hoc analysis.
Be mindful that incremental refresh in dataflows has specific capacity and timeout constraints that differ between shared and premium capacities.
6. Performance tuning and troubleshooting for incremental refresh
Even with a good pattern, incremental refresh performance depends heavily on design choices in Power Query, the data model, and the underlying infrastructure.
6.1 Ensuring query folding
For Power BI incremental refresh, folding is critical. If the filter using RangeStart and RangeEnd does not fold, the data source may end up scanning the full table, which defeats the purpose of incremental refresh. Keep transformations simple above the filtered step and push complex calculations into the source database when possible. Official guidance stresses the importance of filter-based query folding for incremental refresh to be effective.
6.2 Choosing partition granularity and history length
Too many tiny partitions can create overhead, whereas very large partitions reduce the benefit of incremental refresh. Best-practice material for large models generally recommends choosing a partition grain (such as month or day) that balances refresh duration with management complexity, and limiting stored history to only what is needed for reporting and compliance.
6.3 Using 64-bit Excel and appropriate hardware
When implementing manual incremental patterns in Excel, memory can become a bottleneck. Running 64-bit Excel removes the 2 GB memory limit imposed by 32-bit editions and is strongly recommended for large Power Query workloads, along with sufficient RAM and CPU capacity.
6.4 Hybrid tables and real-time requirements
For near real-time scenarios, Power BI offers hybrid tables where historical partitions are imported and recent data is accessed via DirectQuery. This gives you incremental refresh for the bulk of history and real-time data for the latest partition. Such techniques are typically combined with Excel front-ends that connect to the Power BI dataset for unified reporting.
6.5 Scheduling and staggering refresh operations
In environments with many datasets and dataflows, carefully schedule refreshes to avoid resource contention. Staggering refresh times keeps the infrastructure stable and helps important models complete successfully.
7. Implementation checklist for incremental refresh with Excel and Power BI
The following checklist summarizes the main decisions and steps when designing incremental refresh patterns that involve Excel.
- Decide whether incremental refresh will live in Power BI (recommended), a dataflow, or a manual pattern in Excel Power Query.
- Identify the key Date or DateTime column that will drive partitioning or watermark filters.
- For Power BI incremental refresh:
- Create
RangeStartandRangeEndparameters. - Apply parameter-based filters and verify query folding.
- Configure a sensible policy for history and incremental windows.
- Publish, run the initial refresh, and monitor partition behavior.
- Create
- For Excel manual incremental patterns:
- Create History and Config tables in Excel.
- Implement a watermark query in Power Query.
- Filter source queries based on the watermark with an overlap buffer.
- Append new rows to history and maintain the watermark after each refresh.
- Validate that the refreshed data is complete, especially around period boundaries and late-arriving records.
- Document the pattern so that future maintainers understand how incremental refresh works across Excel, Power Query, and Power BI.
FAQ
Does Excel support native incremental refresh like Power BI?
Excel Power Query does not provide native partition-based incremental refresh like Power BI datasets. It can filter and append data, but it does not automatically create or manage partitions in the workbook. To get true incremental refresh with partitions, you must use Power BI incremental refresh policies and optionally connect Excel to that dataset. Community and training materials consistently highlight that Power Query in Excel lacks this built-in incremental refresh feature.
Can I apply incremental refresh to Excel or CSV files stored in SharePoint?
Incremental refresh in Power BI relies on query folding so that date filters based on RangeStart and RangeEnd can be applied at the source. Files such as Excel and CSV generally do not fold in the same way relational databases do, which makes them poor candidates for efficient incremental refresh. Official guidance and community discussions note that incremental refresh works best with structured relational sources and that Excel files in SharePoint often do not support the required folding behavior.
How much history should I keep in an incremental refresh policy?
The optimal amount of history depends on compliance, analytical, and performance requirements. Many performance best-practice guides recommend storing only the years that are actually needed for analysis, such as five to ten years, and using incremental refresh to keep recent data up to date. Reducing unnecessary history can significantly speed up refresh and simplify the model.
What happens to RangeStart and RangeEnd after publishing the model?
In Power BI Desktop, RangeStart and RangeEnd control how much sample data is loaded during development. After publishing and enabling incremental refresh, the Power BI service automatically manages these parameter values for each refresh operation. The engine sets them to values that cover the full history and incremental window defined in your policy, so you do not manually adjust them in the service.
How should I handle late-arriving data with incremental refresh?
Late-arriving data can be handled by configuring the incremental refresh window to cover a slightly wider range than the period where data usually arrives, and by performing occasional full refreshes. In manual Excel patterns, you can pull an overlap window and deduplicate rows based on a primary key, ensuring that both new and corrected records are captured without reloading the entire history on every refresh.