Power BI Incremental Refresh
Enterprise guide to incremental data loading, partition strategy, RangeStart/RangeEnd parameters, real-time hybrid, and refresh optimization.
What Is Incremental Refresh in Power BI?
Quick Answer: Set up incremental refresh in Power BI by creating two DateTime parameters (RangeStart and RangeEnd), filtering your fact table between them, defining a refresh policy that specifies how much historical data to store and how much to refresh each cycle, then publishing to Premium/Fabric. The service automatically partitions data so only recent changes are refreshed — reducing a 4-hour full refresh to minutes. The critical requirement is query folding: the date filter must push down to the source database, not filter in memory.
Incremental refresh is the most important performance optimization for enterprise Power BI datasets. Without it, every scheduled refresh reloads the entire dataset — millions or billions of rows re-queried from the source database, transferred over the network, and reprocessed in the Power BI service. For a dataset with 3 years of sales transactions (500 million rows), a full refresh can take 4-6 hours and place enormous load on the source system.
With incremental refresh, Power BI partitions the dataset by date and only refreshes the most recent partition window. The same 500 million row dataset might only refresh the last 10 days of data — perhaps 5 million rows — completing in 10-15 minutes. Historical partitions are never re-queried. This is not optional for enterprise analytics; it is a requirement for any dataset over 1 GB or any refresh window under 1 hour.
This guide provides the complete implementation path based on EPC Group experience optimizing Power BI deployments for Fortune 500 enterprises. For broader performance optimization including DAX, data model design, and query optimization, see our Power BI Performance Optimization guide.
When to Use Incremental Refresh
Incremental refresh is not always necessary, but it is critical for specific scenarios. Understanding when to apply it prevents over-engineering simple datasets and ensures you optimize where it matters.
Use Incremental Refresh When
- Dataset exceeds 1 GB or contains millions of rows
- Full refresh takes longer than your scheduling window allows
- Source system cannot handle full dataset queries during business hours
- Data has a clear date/time dimension for partitioning
- You need near-real-time data via hybrid DirectQuery
- Historical data rarely changes (append-only or slowly changing)
- You need to refresh more frequently than 8 times per day (Pro limit)
Skip Incremental Refresh When
- Dataset is small (under 100 MB) — full refresh completes in seconds
- Data has no reliable date column for partitioning
- Historical data changes frequently (full table overwrites)
- Source does not support query folding (flat files, web APIs)
- You are using Direct Lake in Fabric (no refresh needed)
- Full DirectQuery mode is sufficient for your latency needs
- Dimension tables that are small and fully replaced each refresh
Step-by-Step Configuration
Follow these six steps to configure incremental refresh in Power BI Desktop and deploy to the Power BI Service. The process takes 15-30 minutes for a single table; enterprise datasets with multiple fact tables may require 1-2 hours of configuration and testing.
Create RangeStart Parameter
In Power Query Editor, click Manage Parameters > New Parameter. Name: RangeStart (exact, case-sensitive). Type: DateTime. Current Value: set to a recent date for development (e.g., 2026-01-01). This parameter defines the start of the date window for incremental loading.
Create RangeEnd Parameter
Create a second parameter. Name: RangeEnd (exact, case-sensitive). Type: DateTime. Current Value: set to today's date (e.g., 2026-04-05). RangeStart and RangeEnd together define the date window that Power BI will use for each partition.
Apply Date Filter to Your Table
Select your fact table in Power Query. Filter the date column: click the column header dropdown > Date/Time Filters > Between. Set "is after or equal to" RangeStart and "is before" RangeEnd. Verify in the formula bar that the filter references the parameters directly — do not add additional transformation steps between the filter and the source.
Verify Query Folding
Right-click the applied step containing the date filter > View Native Query. If you see a SQL query with a WHERE clause referencing the date range, query folding is working. If "View Native Query" is grayed out, the filter is not folding — restructure your query to ensure the date filter pushes down to the source database.
Define Incremental Refresh Policy
In Power BI Desktop, right-click the table in the Fields pane > Incremental refresh and real-time data. Configure: Archive data starting X years/months before refresh date (historical range), Incrementally refresh data starting X days/hours before refresh date (refresh window). Optionally check "Get the latest data in real-time with DirectQuery" for near-real-time.
Publish and Schedule Refresh
Publish the dataset to the Power BI Service (requires Premium, PPU, or Fabric workspace for full functionality). Navigate to the dataset settings > Scheduled refresh > configure the refresh schedule. The first refresh will be a full load to populate all historical partitions — subsequent refreshes will only process the incremental window.
Critical Note: The first refresh after publishing will be a full refresh — it loads all historical data to populate every partition. This initial refresh can take significantly longer than subsequent incremental refreshes. Schedule the first publish during a maintenance window and monitor the refresh until it completes successfully. After the initial full load, all subsequent scheduled refreshes will only process the incremental window.
Partition Strategy and Management
Power BI automatically creates partitions based on your refresh policy, but understanding the partition strategy is essential for troubleshooting, optimization, and advanced scenarios using the XMLA endpoint.
Automatic Partition Granularity
| Historical Range | Partition Granularity | Typical Partition Count |
|---|---|---|
| 5+ years | Yearly partitions | 5-10 historical + daily incremental |
| 1-5 years | Quarterly or monthly partitions | 12-60 historical + daily incremental |
| 3-12 months | Monthly partitions | 3-12 historical + daily incremental |
| Under 3 months | Daily partitions | Varies by range + hourly incremental |
XMLA Endpoint Partition Management
On Premium and Fabric, the XMLA read/write endpoint allows advanced partition management using Tabular Editor, SQL Server Management Studio, or custom scripts. You can view individual partition sizes, merge old daily partitions into monthly partitions (reducing partition count), trigger selective partition refreshes, and customize partition boundaries. This is essential for datasets with hundreds of partitions that need periodic maintenance.
Partition Count Optimization
Keep total partition count under 200 for optimal performance. Datasets with thousands of partitions experience slower metadata operations, longer publish times, and increased memory pressure. If your partition count grows too large, reduce the historical range, increase partition granularity (monthly instead of daily), or merge old partitions via XMLA endpoint.
Schema Change Handling
Adding or removing columns from an incrementally refreshed table requires a full refresh to rebuild all partitions with the new schema. Plan schema changes during maintenance windows. Renaming columns or changing data types also triggers full refresh. To minimize disruption, make schema changes infrequently and batch multiple changes into a single deployment.
Defining the Right Refresh Policy
The refresh policy determines how much data to store, how much to refresh, and how frequently. Getting the policy right balances data freshness, refresh duration, storage consumption, and source system impact.
Historical Range
How many years/months of data to keep in the dataset. Only include history that reports actually query. Storing 10 years when dashboards show 2 years wastes storage and increases partition count. EPC Group default: 3 years for most enterprise datasets, 7 years for compliance-required data.
Incremental Window
How many days/hours of recent data to re-refresh each cycle. Set this to cover the maximum delay for late-arriving data at your source. If source data can arrive 3 days late, set the incremental window to 5 days (with buffer). EPC Group default: 7-10 days for daily ETL, 2-3 days for near-real-time.
Refresh Frequency
How often to run the scheduled refresh. Pro: max 8/day. PPU: max 48/day. Premium: unlimited via API. Match frequency to business need — 8x/day covers most reporting requirements. Use the Enhanced refresh API for on-demand triggers after ETL completion.
Detect Data Changes
Optional: configure a column (like LastModifiedDate) that Power BI checks before refreshing a partition. If the maximum value has not changed since the last refresh, the partition is skipped entirely. This further reduces refresh time when data in the incremental window has not been updated.
Real-Time + Incremental Hybrid Mode
The hybrid mode combines the performance of import mode with the freshness of DirectQuery. Historical and recent data is imported (fast query performance), while a DirectQuery partition covers data since the last refresh (real-time freshness). This is the optimal architecture for enterprise dashboards that need both speed and currency.
How Hybrid Mode Works
Historical Partitions (Import)
Years of historical data stored as compressed columnar storage. Never re-queried from source. Query performance is milliseconds. This is the bulk of your dataset.
Incremental Partitions (Import)
The most recent days/hours of data, refreshed on schedule. Re-imported each cycle to capture updates and late-arriving data. Query performance matches historical.
Real-Time Partition (DirectQuery)
Data from the last refresh to the current moment. Every report query fetches this partition live from the source database. Adds 0-5 seconds latency depending on source performance.
EPC Group Implementation Note: Hybrid mode requires Premium, PPU, or Fabric capacity. The DirectQuery partition performance depends entirely on the source database — ensure the source has appropriate indexes on the date column and can handle concurrent DirectQuery requests from multiple report users. For high-concurrency dashboards (100+ simultaneous users), consider aggregation tables or a dedicated read replica for the DirectQuery partition.
Monitoring Refresh Performance
Enterprise refresh monitoring goes beyond checking "success" or "failure." Track refresh duration trends, per-partition performance, source system impact, and query folding compliance to maintain optimal data loading.
Refresh History Dashboard
- Total refresh duration per run (track 7-day rolling average)
- Rows processed per partition (detect data volume anomalies)
- Success/failure rate (target 99%+ success rate)
- Duration trend line (alert on 50%+ increase over baseline)
Enhanced Refresh API
- Per-partition refresh status: pending, in-progress, complete, failed
- Partition-level duration and row count for bottleneck identification
- Trigger on-demand refresh after ETL pipeline completion
- Retry individual failed partitions without full dataset refresh
XMLA Endpoint Diagnostics
- Connect DAX Studio or SQL Profiler for real-time trace
- Verify query folding — confirm partition queries generate SQL WHERE clauses
- Measure individual partition query duration at the source
- Identify partitions with unexpected row counts or missing data
Azure Log Analytics
- Long-term refresh metrics retention (90+ days)
- Custom alerts for refresh duration, failure, and data anomalies
- Power BI dashboard of refresh health across all enterprise datasets
- Integration with IT operations monitoring and incident management
Common Incremental Refresh Errors
These are the most frequent errors EPC Group encounters when implementing and troubleshooting incremental refresh in enterprise environments. Each error includes the root cause and resolution.
Query Folding Not Detected
Cause: The date filter using RangeStart/RangeEnd is not pushing down to the source database. Common reasons: calculated column used instead of native date column, Power Query transformations break the fold chain, or the data source does not support folding.
Fix: Use a native database date/datetime column. Remove any Power Query steps between the source and the date filter. Test by right-clicking the filter step and checking if "View Native Query" is available. Switch to a foldable source (SQL, Oracle, SAP) if using flat files.
Parameters Not Found or Wrong Type
Cause: RangeStart and/or RangeEnd parameters do not exist, are misspelled (they are case-sensitive), or are the wrong data type (must be DateTime, not Date or Text).
Fix: In Power Query Editor, verify both parameters exist with exact names "RangeStart" and "RangeEnd" of type DateTime. Delete and recreate if uncertain. The DateTime type must include both date and time components.
Dataset Exceeds Size Limit
Cause: Pro workspaces have a 1 GB dataset limit. The historical range creates a dataset larger than allowed.
Fix: Upgrade to Premium Per User (100 GB), Premium capacity (400 GB configurable), or Fabric. Alternatively, reduce the historical range or remove unnecessary columns to reduce dataset size within Pro limits.
Refresh Timeout
Cause: The incremental window is too large, the source database query is slow, or network throughput is insufficient for the data volume.
Fix: Reduce the incremental window (e.g., 3 days instead of 30). Add indexes on the date column at the source. Check source database query performance and network bandwidth. For Premium, increase the refresh timeout in dataset settings.
Inconsistent Data After Partial Failure
Cause: Some partitions refreshed successfully while others failed, leaving the dataset in an inconsistent state with missing or stale data in failed partitions.
Fix: Use the Enhanced refresh API (Premium/Fabric) to retry only the failed partitions. For Pro, trigger a manual refresh which will re-attempt all incremental partitions. If the issue persists, identify the failing partition date range and investigate the source data for that period.
Pro vs Premium vs Fabric: Incremental Refresh Capabilities
The licensing tier determines your incremental refresh capabilities, dataset size limits, and available management tools.
| Capability | Pro | Premium Per User | Premium / Fabric |
|---|---|---|---|
| Max Dataset Size | 1 GB | 100 GB | 400 GB (configurable) |
| Scheduled Refreshes/Day | 8 | 48 | Unlimited (API) |
| Incremental Refresh | Basic | Full | Full + Advanced |
| Real-Time DirectQuery | No | Yes | Yes |
| XMLA Endpoint | No | Read/Write | Read/Write |
| Enhanced Refresh API | No | Yes | Yes + Parallel |
| Partition Management | Automatic only | XMLA customizable | Full XMLA control |
| Direct Lake (Fabric) | No | No | Yes (Fabric only) |
EPC Group Recommendation: For enterprise datasets, Premium Per User (PPU) provides the best cost-to-capability ratio for departmental analytics. For organization-wide critical datasets with high concurrency, Premium capacity or Fabric is required. Pro-only incremental refresh works for small datasets but lacks the management tools and real-time capabilities that enterprise scenarios demand.
Enterprise Best Practices
These best practices are based on EPC Group implementations across Fortune 500 Power BI environments with datasets ranging from 10 GB to 400 GB.
Always verify query folding before publishing — if the date filter does not fold, incremental refresh will load the entire dataset into memory before filtering, defeating the purpose
Index the date/datetime column at the source database — this is the single most impactful optimization for partition query performance
Right-size the historical range to match actual report usage — do not store 10 years if no report queries beyond 3 years
Set the incremental window to cover your maximum late-arriving data delay plus a 2-day buffer
Use the Enhanced refresh API to trigger refreshes after ETL completion instead of fixed schedules — this eliminates wasted refreshes when source data has not changed
Monitor refresh duration trends weekly — a gradual increase indicates growing data volumes or source performance degradation
Schedule partition maintenance quarterly — merge old daily partitions into monthly using XMLA endpoint to keep partition count manageable
Test incremental refresh with a small date range in Desktop before publishing — confirm that RangeStart/RangeEnd filter correctly and query folding works
Document the refresh policy for each dataset in a central governance registry — include historical range, incremental window, schedule, and source system dependencies
Plan schema changes during maintenance windows — column additions/removals trigger full refresh of all partitions
Frequently Asked Questions
How do you set up incremental refresh in Power BI?
Setting up incremental refresh in Power BI requires five steps: 1) Create two DateTime parameters in Power Query named RangeStart and RangeEnd (exact names, case-sensitive), 2) Filter your fact table using these parameters — apply a "between" filter on your date column where dates are >= RangeStart and < RangeEnd, 3) Define the refresh policy in Power BI Desktop — right-click the table > Incremental refresh and real-time data > configure the historical data range to store (e.g., 3 years), the incremental refresh range (e.g., last 10 days), and optionally enable real-time data via DirectQuery, 4) Publish the dataset to the Power BI Service (Premium, Premium Per User, or Fabric capacity required for full functionality), 5) Set up a scheduled refresh in the Power BI Service. The service automatically creates partitions based on your policy — historical partitions are never re-queried, and only the incremental window is refreshed each cycle.
What are the RangeStart and RangeEnd parameters in Power BI?
RangeStart and RangeEnd are two required DateTime parameters that Power BI uses to implement incremental refresh. They must be created in Power Query Editor as parameters of type DateTime with these exact names (case-sensitive). In Power BI Desktop, these parameters filter your data during development — you set them to a small date range for testing. When published to the Power BI Service, the service overrides these parameter values automatically for each partition it creates. For example, if your incremental refresh policy stores 3 years of history with 10-day incremental refresh, the service creates monthly partitions for the 3-year range and a daily partition for the 10-day window, setting RangeStart and RangeEnd for each partition's date range. The key requirement is that your data source must support query folding — the date filter must push down to the source database, not filter in memory.
What is the difference between incremental refresh on Pro vs Premium?
Power BI Pro, Premium, and Fabric have significantly different incremental refresh capabilities: Pro — limited to datasets under 1 GB, maximum 8 refreshes per day scheduled, no XMLA endpoint for partition management, basic incremental refresh only (no real-time). Premium Per User (PPU) — datasets up to 100 GB, 48 refreshes per day, XMLA read/write endpoint for advanced partition management, real-time data via DirectQuery partition, Enhanced refresh API. Premium capacity (P1+) — datasets up to 400 GB (configurable), unlimited API refreshes, full XMLA endpoint, real-time DirectQuery, parallel partition refresh, Enhanced refresh API with full control. Fabric — datasets in OneLake, automatic optimization, Direct Lake mode eliminates refresh for Lakehouse data, XMLA endpoint support. For enterprise datasets over 1 GB, Premium or Fabric is required. EPC Group recommends PPU for departmental datasets and Premium/Fabric for enterprise-critical analytics.
How does Power BI partition data with incremental refresh?
Power BI automatically creates partitions based on your incremental refresh policy — you do not manually create partitions unless using the XMLA endpoint. The partition strategy is: 1) Historical period — Power BI creates partitions at the granularity that best fits the time range (yearly partitions for multi-year ranges, monthly for ranges under 3 years), 2) Incremental refresh period — the most recent period is partitioned at finer granularity (daily or hourly) so that only recent data is refreshed, 3) Real-time partition — if enabled, a DirectQuery partition covers the most current data that has not yet been imported. During scheduled refresh, only partitions within the incremental window are queried from the source — historical partitions are untouched. This dramatically reduces refresh time, source system load, and data transfer. On Premium/Fabric, you can use the XMLA endpoint with tools like Tabular Editor to customize partition boundaries, merge partitions, or trigger individual partition refreshes.
What is the real-time plus incremental hybrid in Power BI?
The real-time plus incremental hybrid combines import mode for historical data with a DirectQuery partition for the most current data. This means: 1) Historical data is imported and stored in the Power BI dataset as compressed columnar storage (fast query performance), 2) The incremental refresh window is also imported but refreshed on schedule (e.g., every 30 minutes), 3) A DirectQuery partition covers data from the last refresh to the current moment — every report query fetches the latest rows directly from the source. The result is near-real-time data freshness with import-mode performance for historical analysis. Requirements: Premium, PPU, or Fabric capacity; the source must support DirectQuery; and query performance depends on source database speed for the real-time partition. EPC Group uses this pattern for executive dashboards that need current-day data alongside historical trends — sales dashboards, operational monitoring, and financial reporting.
How do you monitor incremental refresh performance?
Monitor incremental refresh through multiple tools: 1) Power BI Service refresh history — shows overall refresh duration, rows processed, and success/failure status for each scheduled refresh, 2) Enhanced refresh API — returns per-partition refresh status including duration, rows transferred, and error details (Premium/Fabric only), 3) SQL Server Profiler or DAX Studio — connect via XMLA endpoint to trace partition queries, measure query duration per partition, and identify slow partitions, 4) Azure Log Analytics integration — configure Power BI to send dataset refresh metrics to Log Analytics for long-term trend analysis, alerting, and dashboarding, 5) Source system monitoring — check the database query logs to verify that only incremental partitions generate queries (confirming query folding is working). Key metrics to track: total refresh duration, rows per partition, partition count, query folding confirmation, and refresh failure rate. EPC Group sets up automated alerts for refreshes exceeding baseline duration by 50%.
What are common incremental refresh errors and how do you fix them?
The most common incremental refresh errors: 1) "Query folding not detected" — the date filter is not pushing down to the source; fix by ensuring the date column is a native database type (not a calculated column) and the filter uses direct comparison operators, 2) "RangeStart/RangeEnd not found" — parameter names are case-sensitive; verify exact spelling and type (DateTime, not Date), 3) "Dataset exceeds size limit" — Pro has a 1 GB limit; upgrade to PPU or Premium, or reduce the historical range, 4) "Refresh timeout" — the incremental window is too large; reduce the refresh period or optimize source queries with indexes, 5) "Partition errors after schema change" — adding/removing columns requires a full refresh to rebuild all partitions; plan schema changes during maintenance windows, 6) "Inconsistent data after partial refresh failure" — some partitions refreshed while others failed; use the Enhanced refresh API to retry only failed partitions. EPC Group troubleshooting starts with XMLA endpoint inspection using Tabular Editor to verify partition boundaries and refresh status.
When should you use incremental refresh vs Direct Lake in Fabric?
Incremental refresh and Direct Lake serve different architectures: Incremental refresh is for import-mode datasets that connect to external data sources (SQL Server, Azure SQL, Synapse, Dataverse) — data is copied into the Power BI dataset on a schedule. Direct Lake is a Fabric-native mode where Power BI reads directly from Delta tables in OneLake without importing — no refresh process needed because the data is already in the Lakehouse. Use incremental refresh when: your data source is not in Fabric, you need complex Power Query transformations, or your source does not support Direct Lake. Use Direct Lake when: your data pipeline lands data in Fabric Lakehouse or Warehouse, you want to eliminate refresh latency entirely, and your data model can work with the Direct Lake limitations (no Power Query transformations, limited DAX). EPC Group recommends Direct Lake for new Fabric-native analytics projects and incremental refresh for hybrid architectures with external data sources.
How do you optimize incremental refresh for large enterprise datasets?
Enterprise optimization strategies for incremental refresh: 1) Right-size the historical window — store only the years of history that reports actually query; extra history increases partition count and storage without user benefit, 2) Optimize the incremental window — a 3-day window refreshes faster than a 30-day window; match the window to your data latency tolerance, 3) Index the date column at the source — the partition query filters on your date column; a clustered index or partition on this column at the source dramatically reduces query time, 4) Enable query folding verification — use Power Query diagnostics to confirm that the RangeStart/RangeEnd filter folds to the source; if not, restructure the query, 5) Use the Enhanced refresh API for parallel processing — Premium and Fabric support parallel partition refresh, reducing total refresh time for datasets with many partitions, 6) Schedule refreshes during off-peak hours — reduce contention with source system workloads, 7) Partition management via XMLA — use Tabular Editor to merge old daily partitions into monthly, keeping the partition count manageable. EPC Group has optimized enterprise datasets from 4-hour full refreshes down to 15-minute incremental refreshes.
Related Resources
Power BI Consulting Services
Enterprise Power BI implementation, performance optimization, incremental refresh configuration, and governance from EPC Group.
Read morePower BI Performance Optimization
Complete guide to DAX optimization, data model design, query performance, and enterprise-scale Power BI architecture.
Read moreEnterprise Analytics Solutions
Microsoft analytics platform strategy including Power BI, Fabric, Synapse, and enterprise data architecture.
Read moreNeed Power BI Refresh Optimization?
EPC Group optimizes Power BI incremental refresh for enterprise datasets — reducing multi-hour full refreshes to minutes, configuring hybrid real-time mode, implementing partition management strategies, and establishing monitoring dashboards. Our Power BI performance engagements typically deliver 10-50x refresh time improvement within the first two weeks.