Power BI Incremental Refresh: Advanced Patterns for Enterprise Data at Scale
Master advanced incremental refresh patterns including partition management, RangeStart/RangeEnd, and hybrid table architectures for large datasets.
Incremental refresh is essential for Power BI datasets with millions or billions of rows, dramatically reducing refresh times from hours to minutes. This advanced guide covers partition strategies, detecting data changes, hybrid tables, and troubleshooting complex scenarios. Our Power BI consulting services implement incremental refresh for Fortune 500 enterprises processing terabytes of data daily.
Why Incremental Refresh Matters
The Full Refresh Problem
Traditional Power BI datasets refresh ALL data on every refresh:
Example: Sales fact table with 500 million rows
| Metric | Full Refresh | Incremental Refresh | |---|---|---| | Refresh time | 4-6 hours | 5-15 minutes | | Network transfer | 80 GB | 2 GB | | Source database load | Full table scan (high CPU/IO) | Index seek on DateKey (minimal) | | Failure recovery | Start over from 0% | Retry only failed partition | | Refresh frequency | Once daily (overnight) | Hourly or more | | Capacity cost | High (long-running processing) | Low (short bursts) |
Business Impact
Without Incremental Refresh: overnight refresh windows become insufficient as data grows, reports show stale data by mid-morning, refresh failures disrupt operations with no partial recovery, and Premium capacity costs escalate as processing time increases.
With Incremental Refresh: hourly refreshes become feasible, near real-time reporting is achievable, 95%+ refresh success rate due to smaller partition windows, and capacity costs drop significantly as only recent data is processed.
RangeStart and RangeEnd Parameters
The foundation of incremental refresh is two Power Query parameters that define the refresh window:
Setting up parameters: 1. In Power Query Editor, create two parameters named exactly RangeStart and RangeEnd (case-sensitive) 2. Set both types to Date/Time (not Date—this is a common error) 3. Set default values for development (e.g., RangeStart = 1/1/2024, RangeEnd = 2/1/2024) 4. Apply a filter on your date column: `DateColumn >= RangeStart AND DateColumn < RangeEnd`
Critical requirement: The filter must occur at a step where query folding is active. If you apply the filter after a step that breaks folding (like a custom function or merge), the entire table is downloaded and filtered locally—defeating the purpose entirely. Verify by right-clicking the filtered step and checking if "View Native Query" is available.
Common mistakes: - Naming parameters "rangestart" (wrong case) or "Range_Start" (wrong format) - Using Date type instead of Date/Time - Filtering with `<=` for RangeEnd instead of `<` (causes overlapping partitions) - Applying the filter after a merge step that breaks query folding
Partition Strategy Design
Incremental refresh works by splitting your table into time-based partitions. The policy defines two windows:
Archive window: Historical data stored in read-only partitions. These partitions never refresh after initial load, reducing total processing. Typical setting: 3-5 years for most enterprises.
Incremental window: Recent data that refreshes on every scheduled refresh. Only partitions within this window are re-queried from the source. Typical setting: 7-30 days depending on data latency requirements.
Partition granularity: Power BI automatically determines partition grain based on your archive window: - Archive window < 1 year → daily partitions - Archive window 1-3 years → monthly partitions - Archive window > 3 years → quarterly or yearly partitions
For maximum control over partition granularity, use the XMLA endpoint with tools like Tabular Editor to create custom partition schemes.
Detecting Data Changes
By default, incremental refresh re-queries all rows in the incremental window on every refresh. For tables where only a small percentage of recent rows actually change, you can optimize further with change detection:
Enable "Detect data changes" in the incremental refresh policy and select a column that indicates when a row was last modified (e.g., LastModifiedDate, UpdatedTimestamp). Power BI queries the maximum value of this column for each partition. If the max value has not changed since last refresh, the partition is skipped entirely.
Impact: For a 30-day incremental window where only today's data changes, this reduces the refresh scope from 30 partitions to 1 partition—a 97% reduction in processing.
Requirements: The change detection column must support query folding to the source, and it must reliably update when any row in the partition changes.
Hybrid Tables (Premium Feature)
Hybrid tables combine Import mode for historical data with DirectQuery for the most recent partition, giving you near real-time data without waiting for the next refresh:
How it works: 1. Historical and incremental partitions remain in Import mode (fast queries) 2. A special DirectQuery partition covers data from the last refresh until "now" 3. Users always see current data without any staleness gap 4. When the next refresh runs, the DirectQuery partition data moves into Import
Configuration: Enable "Get the latest data in real time with DirectQuery" in the incremental refresh policy. This requires Premium or Fabric capacity—not available in Pro or PPU.
Use cases: Financial trading dashboards, operational monitoring, IoT sensor data, and any scenario where even hourly data latency is unacceptable.
XMLA Endpoint Partition Management
For advanced partition control beyond what the GUI offers, use the XMLA endpoint with tools like Tabular Editor or SQL Server Management Studio (SSMS):
View existing partitions: Connect to the dataset via XMLA, expand the table, and view all partitions with their date ranges, row counts, and last refresh times.
Manual partition operations: - Delete old partitions to reduce dataset size without changing the policy - Merge small partitions into larger ones for efficiency - Force refresh specific partitions after data corrections - Create custom partition boundaries that do not align with calendar periods
Automated partition management: Use the Enhanced Refresh API with REST endpoints to programmatically refresh specific partitions, check refresh status, and orchestrate complex refresh workflows from Azure Data Factory or Power Automate.
Troubleshooting Common Issues
"RangeStart and RangeEnd parameters are required" error: Parameters must be named exactly RangeStart and RangeEnd (case-sensitive), typed as Date/Time, and the filter must be applied before any folding-breaking step.
Refresh takes longer than expected: Check if query folding is occurring on the date filter step. If the source has no index on the date column, every refresh performs a full table scan filtered in memory.
Data discrepancies after enabling incremental refresh: Historical partitions are immutable. If source data was retroactively corrected, those corrections do not appear in archived partitions. Use XMLA to manually refresh affected historical partitions.
Partition count exceeds limits: Power BI supports up to 10,000 partitions per table. With daily partitions over 5+ years, you may hit this limit. Switch to monthly partitions for older archive periods using XMLA endpoint partition management.
Ready to implement incremental refresh at enterprise scale? Contact our team for data architecture consultation.
Related Resources
Frequently Asked Questions
Can I use incremental refresh with DirectQuery tables?
No, incremental refresh only works with Import mode tables in Power BI. DirectQuery tables do not store data locally, so there are no partitions to refresh incrementally. However, you can use hybrid tables (Premium feature) which combine Import mode (historical data with incremental refresh) and DirectQuery (recent real-time data) in a single table. This gives you the performance of Import for history and real-time for recent data. Alternatively, consider migrating to Direct Lake mode in Microsoft Fabric, which provides DirectQuery-like real-time access with Import-like performance without requiring incremental refresh.
What happens to historical partitions if source data is deleted?
Historical partitions in Power BI remain unchanged even if source data is deleted—they are not automatically removed. Power BI only refreshes partitions within the incrementally refresh data starting window. If you need to remove historical data, you have two options: (1) Edit the incremental refresh policy to reduce the archive window (e.g., from 5 years to 3 years), which deletes old partitions on next refresh, or (2) Use XMLA endpoint with Tabular Editor to manually delete specific partitions. Many organizations leverage this behavior as a data retention strategy—source databases purge old data for cost savings, but Power BI retains historical reporting data in partitions.
How do I troubleshoot RangeStart and RangeEnd parameters are required error?
This error occurs when incremental refresh cannot find properly configured parameters. Ensure: (1) Parameters named exactly RangeStart and RangeEnd (case-sensitive), (2) Type set to Date/Time (not Date), (3) Filter applied using >= for RangeStart and < for RangeEnd, (4) Filter applied in Power Query before any transformations that break query folding. Common mistake: applying filter AFTER a merge or custom function that prevents folding. Verify with View Native Query on the filtered step—if greyed out, rearrange steps to filter earlier. If error persists after publishing, check Service dataset settings to ensure incremental refresh policy saved correctly—sometimes it does not persist on first publish.