Power BI Incremental Refresh & Data Partitioning: The Definitive Enterprise Guide for 2026
Master Power BI incremental refresh and data partitioning to cut refresh times by 95%. Enterprise strategies for datasets exceeding 100GB.
<h1>Power BI Incremental Refresh & Data Partitioning: The Definitive Enterprise Guide for 2026</h1>
<p>Enterprise Power BI deployments routinely manage datasets spanning hundreds of millions of rows across multi-terabyte data warehouses. Without incremental refresh and deliberate partition management, every scheduled refresh reloads the entire dataset from scratch, consuming hours of Premium capacity, saturating network bandwidth, and hammering source databases with full table scans. In 2026, with Fabric capacity pricing and the maturity of XMLA endpoint tooling, there is no excuse for running full refreshes on datasets exceeding a few hundred megabytes. This guide covers everything your data engineering team needs to implement production-grade incremental refresh with optimized partition strategies. Our <a href="/services/power-bi-architecture">Power BI architecture consulting</a> team has deployed these patterns across Fortune 500 organizations processing over 2 billion rows daily.</p>
<h2>What Is Incremental Refresh and Why It Matters</h2>
<p>Incremental refresh is a Power BI dataset configuration that partitions Import-mode tables by date and refreshes only the partitions containing recent or changed data. Instead of reloading 500 million rows on every refresh, the engine identifies which time-based partitions fall within the configured refresh window and queries only those partitions from the source. Historical partitions outside the refresh window remain untouched in storage.</p>
<p>The business impact is substantial. Organizations that implement incremental refresh consistently report the following improvements:</p>
<ul> <li><strong>Refresh time reduction of 90-98%</strong> — A dataset that previously required 4 hours to refresh completes in under 10 minutes when only the last 7 days of data are re-queried.</li> <li><strong>Source database load reduction</strong> — Instead of executing a full table scan across the entire fact table, the source query targets a narrow date range that hits a clustered index seek, reducing CPU and I/O by orders of magnitude.</li> <li><strong>Failure resilience</strong> — When a refresh fails mid-process, only the affected partition needs to be retried. Historical partitions are not impacted, and partial refresh recovery is automatic on the next scheduled run.</li> <li><strong>Higher refresh frequency</strong> — With refresh operations completing in minutes rather than hours, organizations can schedule refreshes every 30 minutes instead of once daily, delivering significantly fresher data to business users.</li> <li><strong>Reduced capacity cost</strong> — Shorter refresh operations consume fewer Fabric CU-seconds, directly lowering the cost per refresh cycle. For organizations running multiple datasets on shared Premium or Fabric capacity, this can free up enough headroom to avoid upgrading to a higher SKU.</li> </ul>
<p>For organizations managing enterprise-scale deployments, incremental refresh is not an optimization — it is a prerequisite. Learn more about our approach at <a href="/services/enterprise-deployment">enterprise deployment services</a>.</p>
<h2>RangeStart and RangeEnd Parameter Configuration</h2>
<p>The foundation of incremental refresh is two Power Query parameters that define the date window for each partition query. These parameters must be configured precisely — any deviation causes silent failures or full-table refreshes that defeat the purpose entirely.</p>
<h3>Step-by-Step Setup</h3>
<ol> <li><strong>Open Power Query Editor</strong> in Power BI Desktop and navigate to Manage Parameters.</li> <li><strong>Create a parameter named exactly <code>RangeStart</code></strong> (case-sensitive). Set the type to <code>Date/Time</code> — not <code>Date</code>, not <code>Text</code>. Using the wrong type is the single most common configuration error. Set a default value such as <code>1/1/2025 12:00:00 AM</code> for development purposes.</li> <li><strong>Create a second parameter named exactly <code>RangeEnd</code></strong> with identical type settings. Set its default to <code>2/1/2025 12:00:00 AM</code> to define a one-month development window.</li> <li><strong>Apply a filter on your date column</strong> in the table that will be incrementally refreshed. The filter logic must be: <code>DateColumn >= RangeStart AND DateColumn < RangeEnd</code>. Note the strict less-than on RangeEnd — using less-than-or-equal creates overlapping partitions that duplicate rows.</li> <li><strong>Verify the M code</strong> in the Advanced Editor. The filter step should look like: <code>Table.SelectRows(PreviousStep, each [OrderDate] >= RangeStart and [OrderDate] < RangeEnd)</code>.</li> </ol>
<h3>Critical Configuration Rules</h3>
<ul> <li><strong>Parameter names are case-sensitive.</strong> `rangestart`, `Range_Start`, or `RANGESTART` will not be recognized. The incremental refresh configuration dialog will not appear if parameters are incorrectly named.</li> <li><strong>Type must be Date/Time, not Date.</strong> Power BI internally uses DateTime values for partition boundaries. A Date-typed parameter truncates time components, causing partition boundary misalignment.</li> <li><strong>Filter must use >= and < operators.</strong> The inclusive lower bound and exclusive upper bound ensure each row belongs to exactly one partition. Using <= on RangeEnd causes rows at midnight partition boundaries to appear in two partitions.</li> <li><strong>Default values are for development only.</strong> After publishing, Power BI Service overwrites these values dynamically based on the incremental refresh policy. Do not hardcode production date ranges.</li> </ul>
<h2>Query Folding: The Make-or-Break Requirement</h2>
<p>Query folding is the process by which Power Query translates M code operations into native source queries (T-SQL, PL/SQL, etc.) that execute on the source database rather than in the Power BI engine. For incremental refresh to deliver its performance benefits, the RangeStart/RangeEnd filter <strong>must fold</strong> to the source. If it does not fold, Power BI downloads the entire table and filters locally — negating every benefit of incremental refresh.</p>
<h3>How to Verify Query Folding</h3>
<ol> <li>In Power Query Editor, right-click the filtered step (the step where you applied the RangeStart/RangeEnd filter).</li> <li>If <strong>“View Native Query”</strong> is available and not greyed out, the step folds. Click it to see the generated SQL — you should see a WHERE clause with date comparisons.</li> <li>If “View Native Query” is greyed out, folding is broken at or before this step. You need to restructure your query to ensure the date filter occurs before any folding-breaking operation.</li> </ol>
<h3>Common Folding Breakers</h3>
<ul> <li><strong>Custom M functions</strong> applied before the date filter break folding for most sources.</li> <li><strong>Table.Combine or Table.Join</strong> with non-foldable sources prevent downstream folding.</li> <li><strong>Type transformations on the date column</strong> (e.g., converting text to date) before filtering break folding. Ensure your source column is already a date/datetime type.</li> <li><strong>Added columns using M expressions</strong> that reference non-foldable functions prevent the date filter from folding if applied before the filter step.</li> <li><strong>Merging queries from different sources</strong> always breaks folding because no single source can execute a cross-source join natively.</li> </ul>
<h3>Folding Optimization for SQL Server Sources</h3>
<p>For SQL Server and Azure SQL Database sources (the most common in enterprise deployments), ensure the date column used in the filter has a <strong>clustered or non-clustered index</strong>. Without an index, the folded query still performs a full table scan — it simply does so on the database side rather than in Power BI. With a proper index, the query performs an index seek that returns only the rows in the partition window, typically completing in seconds regardless of total table size.</p>
<h2>Partition Strategies: Daily, Monthly, and Yearly</h2>
<p>Power BI automatically determines partition granularity based on the archive window duration you configure in the incremental refresh policy. However, understanding the implications of each granularity is critical for performance tuning.</p>
<h3>Automatic Partition Grain</h3>
<table> <thead> <tr><th>Archive Window</th><th>Automatic Grain</th><th>Partition Count (5 Years)</th><th>Best For</th></tr> </thead> <tbody> <tr><td>Less than 1 year</td><td>Daily</td><td>~365</td><td>High-frequency data with short retention</td></tr> <tr><td>1-3 years</td><td>Monthly</td><td>60</td><td>Most enterprise scenarios</td></tr> <tr><td>Greater than 3 years</td><td>Quarterly/Yearly</td><td>20 or 5</td><td>Long-term historical reporting</td></tr> </tbody> </table>
<h3>Daily Partitions</h3>
<p>Daily partitions provide the finest granularity. Each day of data occupies its own partition, enabling highly targeted refreshes. The trade-off is partition count: 5 years of daily partitions creates 1,825 partitions per table. Power BI supports up to 10,000 partitions per table, but very high partition counts increase metadata overhead during refresh operations and can slow down the partition management process itself.</p>
<p><strong>When to use daily partitions:</strong> Datasets with less than 1 year of history, intraday refresh requirements, or scenarios where individual days of data may need to be selectively refreshed via XMLA endpoint.</p>
<h3>Monthly Partitions</h3>
<p>Monthly partitions are the most common choice for enterprise deployments. They balance granularity with manageability — 5 years of monthly partitions creates only 60 partitions per table. The refresh window typically spans 1-3 months, meaning only 1-3 partitions are re-queried on each refresh cycle.</p>
<p><strong>When to use monthly partitions:</strong> Most enterprise scenarios with 1-5 years of history, overnight refresh schedules, and datasets where source data corrections are typically made within the current month.</p>
<h3>Yearly Partitions</h3>
<p>Yearly partitions minimize partition count but create very large individual partitions. If a yearly partition needs to be refreshed (due to a data correction or policy change), the engine must reload an entire year of data. This is acceptable for truly static historical data but problematic if retroactive corrections are common.</p>
<p><strong>When to use yearly partitions:</strong> Regulatory archives with 7+ years of retention, static reference datasets, or scenarios where historical data is guaranteed never to change after initial load.</p>
<h3>Custom Hybrid Partition Strategies via XMLA</h3>
<p>The most sophisticated deployments use hybrid strategies: monthly partitions for recent years and yearly partitions for older data. This is not configurable through the Power BI Desktop GUI — it requires XMLA endpoint access with Tabular Editor. For example, a 10-year dataset might use daily partitions for the current month, monthly partitions for years 1-3, and yearly partitions for years 4-10. This delivers fast refreshes for recent data while minimizing total partition count. Our <a href="/services/data-analytics">data analytics team</a> implements these hybrid strategies for clients with complex retention requirements.</p>
<h2>Real-Time Data with DirectQuery Partitions</h2>
<p>Hybrid tables — available exclusively on Premium and Fabric capacities — combine Import mode partitions with a single DirectQuery partition that covers data from the last refresh until the present moment. This eliminates the staleness gap between scheduled refreshes without sacrificing the query performance of Import mode for historical data.</p>
<h3>How DirectQuery Partitions Work</h3>
<ol> <li>All historical and incremental partitions remain in Import mode, stored in the VertiPaq engine for sub-second query performance.</li> <li>A special DirectQuery partition automatically covers the time range from the end of the last Import refresh to the current timestamp.</li> <li>When a user runs a report query, the engine combines results from Import partitions (fast, cached) and the DirectQuery partition (live, real-time) transparently.</li> <li>On the next scheduled refresh, data from the DirectQuery window is imported into a new Import partition, and the DirectQuery partition resets to cover only the newest data.</li> </ol>
<h3>Enabling DirectQuery Partitions</h3>
<p>In the incremental refresh policy dialog, enable <strong>“Get the latest data in real time with DirectQuery”</strong>. This option is only available when the dataset is published to a Premium or Fabric workspace. It requires that the source supports DirectQuery mode — SQL Server, Azure SQL, Synapse, Dataverse, and several other enterprise sources qualify.</p>
<h3>Performance Considerations</h3>
<ul> <li><strong>Source database must handle live queries.</strong> Every report interaction generates a DirectQuery call to the source for the real-time partition. Ensure the source can handle the concurrent query load from all report consumers.</li> <li><strong>Aggregations help.</strong> Define aggregations on Import partitions so that queries spanning historical + real-time data hit the Import aggregation cache for most of the calculation, with only the real-time slice going to DirectQuery.</li> <li><strong>Network latency matters.</strong> DirectQuery performance depends on round-trip latency to the source. For on-premises sources accessed through a data gateway, ensure the gateway is deployed in the same network region as the Fabric capacity.</li> <li><strong>Monitor with Performance Analyzer.</strong> Use the built-in Performance Analyzer in Power BI Desktop and the Capacity Metrics app in the Service to identify if the DirectQuery partition is becoming a bottleneck. If DirectQuery query durations exceed 3 seconds, consider increasing refresh frequency to shrink the DirectQuery window.</li> </ul>
<h2>XMLA Endpoint for Advanced Partition Management</h2>
<p>The XMLA (XML for Analysis) endpoint exposes the Tabular Object Model (TOM) of a Power BI dataset, enabling programmatic partition management that goes far beyond the GUI capabilities. This is essential for enterprise deployments where automated DevOps pipelines manage dataset lifecycle.</p>
<h3>Connecting to the XMLA Endpoint</h3>
<p>The XMLA endpoint URL follows the pattern: <code>powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName</code>. Connect using SQL Server Management Studio (SSMS), Tabular Editor, or any XMLA-compatible client library (.NET TOM, Python pyadomd). Authentication uses Azure AD / Entra ID — the connecting account needs at minimum the Contributor role on the workspace plus the “Allow XMLA endpoints” tenant setting enabled by the Power BI admin.</p>
<h3>Partition Operations via XMLA</h3>
<ul> <li><strong>List all partitions</strong> — View every partition in a table with its date range, row count, last refresh timestamp, and current state (ready, calculating, or invalid).</li> <li><strong>Selectively refresh partitions</strong> — Refresh only specific partitions by name or date range. This is critical when a data correction affects a single month in a 5-year dataset — refresh only that month’s partition instead of the entire incremental window.</li> <li><strong>Delete old partitions</strong> — Remove historical partitions that are no longer needed without modifying the incremental refresh policy. Useful for compliance-driven data deletion requirements (GDPR right to erasure, HIPAA retention limits).</li> <li><strong>Merge partitions</strong> — Combine multiple fine-grained partitions (e.g., 30 daily partitions) into a single coarser partition (1 monthly partition) to reduce total partition count without losing data.</li> <li><strong>Create custom partitions</strong> — Define partitions with arbitrary date boundaries that do not align with calendar periods. For example, create partitions aligned to fiscal quarters or business-specific reporting periods.</li> </ul>
<h3>Enhanced Refresh API for Automation</h3>
<p>The Enhanced Refresh REST API (available via <code>POST /groups/{groupId}/datasets/{datasetId}/refreshes</code>) allows programmatic control over refresh operations. Key capabilities include specifying individual partitions or tables to refresh, setting `commitMode` to `transactional` (atomic) or `partialBatch` (best-effort), polling refresh status via <code>GET /refreshes/{refreshId}</code>, and integrating with Azure Data Factory, Power Automate, or custom orchestration pipelines.</p>
<p>This API is how mature organizations orchestrate end-to-end data pipelines: source ETL completes, triggers a webhook, which calls the Enhanced Refresh API targeting only the partitions affected by the ETL run. This event-driven pattern eliminates the need for fixed refresh schedules and ensures data is always current within minutes of source updates.</p>
<h2>Refresh Policies: Rolling Window Patterns</h2>
<p>The incremental refresh policy defines two critical windows that control which partitions are refreshed and which are archived.</p>
<h3>Archive Window</h3>
<p>The archive window defines how far back in history the dataset retains data. Partitions within this window are loaded once (during initial dataset publish or when new historical data enters the window) and then never refreshed again. This is the read-only historical tier.</p>
<p>Setting the archive window requires balancing data retention requirements against dataset size. Each additional year of archived data increases the compressed dataset size. For datasets approaching the 10GB, 25GB, or 100GB per-dataset limits (depending on SKU), the archive window directly impacts whether the dataset fits within capacity constraints.</p>
<h3>Incremental Window</h3>
<p>The incremental window defines the rolling period of data that is re-queried from the source on every scheduled refresh. All partitions within this window are refreshed, and any new or changed rows are imported.</p>
<p>Typical configurations by industry:</p>
<ul> <li><strong>7-day incremental window</strong> — Suitable for retail and e-commerce where source data corrections are applied within a week and refresh frequency is daily or more.</li> <li><strong>30-day incremental window</strong> — Common for financial services datasets where month-end adjustments may retroactively modify data up to 30 days back.</li> <li><strong>90-day incremental window</strong> — Required for healthcare claims processing, insurance policy adjustments, and government reporting where reconciliation cycles span multiple months.</li> </ul>
<h3>Detect Data Changes Optimization</h3>
<p>Within the incremental window, not every partition necessarily has changed data. The “Detect data changes” option lets you specify a column (such as `LastModifiedTimestamp`) that Power BI checks before refreshing each partition. If the maximum value of this column in a partition has not changed since the last refresh, that partition is skipped entirely.</p>
<p>For a 30-day incremental window where only the last 2 days of data have changed, this reduces refresh scope from 30 partitions to 2 — a 93% reduction within the already-reduced incremental window. This is especially impactful for hourly refresh schedules where most of the incremental window is unchanged between consecutive runs.</p>
<h2>Monitoring Refresh Performance</h2>
<p>Implementing incremental refresh is only the first step. Ongoing monitoring ensures that the configuration remains optimal as data volumes grow and usage patterns evolve.</p>
<h3>Power BI Premium Capacity Metrics App</h3>
<p>The Capacity Metrics app provides detailed per-dataset refresh metrics including total refresh duration, CPU seconds consumed, memory consumption during refresh, individual partition refresh times, and refresh queue wait times (indicating capacity contention). Monitor for trends: if incremental refresh duration is gradually increasing, the incremental window may need adjustment or the source database may need index optimization.</p>
<h3>Refresh History in Power BI Service</h3>
<p>Navigate to dataset settings and view refresh history for the last 60 refresh operations. Each entry shows start time, end time, duration, status (succeeded/failed), and the type of refresh (on-demand, scheduled, API-triggered). Failed refreshes include error messages that typically identify the root cause — timeout, authentication failure, source unavailable, or partition-specific errors.</p>
<h3>DMV Queries via XMLA</h3>
<p>For advanced monitoring, execute Dynamic Management View (DMV) queries against the XMLA endpoint. The query <code>SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS</code> returns the complete partition inventory with row counts, sizes, and refresh timestamps. The query <code>SELECT * FROM $SYSTEM.DISCOVER_SESSIONS</code> shows active refresh sessions. These DMVs can be scheduled via Power Automate or Azure Logic Apps to build custom monitoring dashboards that alert on refresh duration anomalies, partition count growth, or failed refresh patterns.</p>
<h3>Log Analytics Integration</h3>
<p>For enterprise-grade observability, export Power BI activity logs to Azure Log Analytics. This captures every refresh event with detailed telemetry including partition-level timing, data volumes transferred, and error stack traces. Build KQL dashboards in Azure Monitor that correlate refresh performance with capacity utilization, enabling proactive capacity planning before users experience degradation.</p>
<h2>Troubleshooting Common Issues</h2>
<h3>“Incremental refresh policy not applied” After Publishing</h3>
<p>This occurs when the dataset is published to a Pro workspace instead of Premium/Fabric. Incremental refresh basic features work with Pro, but advanced features (hybrid tables, XMLA partition management, Enhanced Refresh API) require Premium Per User (PPU), Premium capacity (P1+), or Fabric (F2+). Verify the workspace is assigned to a Premium or Fabric capacity before publishing.</p>
<h3>Refresh Duration Not Decreasing After Configuration</h3>
<p>The most common cause is broken query folding. The RangeStart/RangeEnd filter is not being pushed to the source, so Power BI downloads the entire table on every refresh and filters locally. Verify folding using the “View Native Query” check described earlier. Also confirm that the source date column has a supporting index — a folded query without an index still performs a full table scan on the database side.</p>
<h3>Row Count Discrepancies Between Source and Dataset</h3>
<p>Historical (archived) partitions are immutable once loaded. If source data is retroactively modified outside the incremental window, those changes are not reflected in the dataset. Solutions include expanding the incremental window to cover the correction period, using XMLA to manually refresh affected partitions, or scheduling a one-time full refresh using the Enhanced Refresh API with <code>"type": "full"</code>.</p>
<h3>“Partition key column not found” Error</h3>
<p>The RangeStart/RangeEnd parameters are not correctly applied, or the date column referenced in the filter was renamed or removed in a subsequent Power Query step. Ensure the date column name is consistent from the source through to the filter step, and that parameters are named exactly `RangeStart` and `RangeEnd` with `Date/Time` type.</p>
<h3>Partition Count Approaching 10,000 Limit</h3>
<p>Power BI supports a maximum of 10,000 partitions per table. Daily partitions over 27+ years would hit this limit. Use XMLA endpoint to merge older daily partitions into monthly or yearly partitions. Consider reducing the archive window or switching to a coarser partition grain for historical periods.</p>
<h3>DirectQuery Partition Returns Stale Data</h3>
<p>If the hybrid table’s DirectQuery partition appears to show outdated data, check that the source database is not returning cached results (some connection providers and connection pooling configurations cache query results). Also verify that the data gateway (if used) is running the latest version and that the gateway machine has adequate memory and is not experiencing resource pressure that causes query timeouts or connection drops.</p>
<h3>Initial Full Refresh Timeout</h3>
<p>The first refresh after configuring incremental refresh loads ALL historical data into partitions. For large datasets, this initial load can exceed the default 2-hour timeout. Increase the timeout via XMLA endpoint before the first publish, or use the Enhanced Refresh API with an extended timeout parameter. Alternatively, reduce the initial archive window, let it load successfully, then gradually expand it in subsequent publishes.</p>
<h2>Best Practices for Datasets Over 1GB</h2>
<p>Large datasets demand additional attention beyond basic incremental refresh configuration. These practices are drawn from our <a href="/services/power-bi-architecture">Power BI architecture engagements</a> with enterprises managing datasets from 1GB to 100GB+.</p>
<h3>Model Optimization Before Incremental Refresh</h3>
<ul> <li><strong>Reduce cardinality.</strong> High-cardinality text columns (transaction IDs, GUIDs, full addresses) inflate dataset size dramatically. Remove or hash columns that are not used in reports or DAX calculations.</li> <li><strong>Use appropriate data types.</strong> Whole numbers compress better than decimals. Date keys (YYYYMMDD integers) compress better than datetime columns. Fixed-length strings compress better than variable-length text.</li> <li><strong>Disable Auto Date/Time.</strong> The automatic date hierarchy creates hidden date tables for every date column, adding significant overhead in large models. Create a single shared date dimension table instead and mark it as the date table.</li> <li><strong>Remove unused columns and tables.</strong> Every column in an Import table consumes memory and storage. Audit the model quarterly using DAX Studio VertiPaq Analyzer to identify columns not referenced in reports, measures, or RLS rules.</li> <li><strong>Implement aggregation tables.</strong> For fact tables exceeding 100 million rows, create pre-aggregated summary tables at higher grains (monthly, quarterly) and configure user-defined aggregations. Queries that can be satisfied by the aggregation table never touch the detail partitions.</li> </ul>
<h3>Large Dataset Storage Format</h3>
<p>Enable <strong>Large Dataset Storage Format</strong> in dataset settings for datasets that may exceed 10GB. This setting uses an optimized storage engine that supports datasets up to the capacity limit (100GB for P1, 400GB for P3, unlimited for Fabric F64+). Without this setting enabled, datasets are capped at 10GB regardless of capacity SKU. Note that enabling this setting is irreversible — the dataset cannot be switched back to standard storage format. Plan for this from the initial deployment.</p>
<h3>Refresh Scheduling Strategy</h3>
<ul> <li><strong>Stagger refresh schedules</strong> across datasets sharing the same capacity. If 20 datasets all refresh at midnight, they queue and compete for processing resources. Spread refreshes across a 2-hour window to reduce contention and maximize throughput.</li> <li><strong>Use the Enhanced Refresh API</strong> instead of scheduled refresh for critical datasets. API-triggered refreshes can be orchestrated to run immediately after upstream ETL completes, eliminating unnecessary wait time between data availability and Power BI refresh.</li> <li><strong>Set appropriate timeout values.</strong> The default refresh timeout is 2 hours for Premium. For very large initial loads, increase this via the XMLA endpoint or API parameters before the first publish.</li> <li><strong>Implement retry logic.</strong> Transient network errors, gateway restarts, and source database failovers can cause refresh failures. Build retry logic (3 attempts with exponential backoff) into your orchestration pipeline using Power Automate or Azure Data Factory.</li> </ul>
<h3>Backup and Recovery</h3>
<p>Use the <strong>dataset backup and restore</strong> feature (Premium/Fabric) to create point-in-time snapshots before making partition changes via XMLA. A misconfigured partition merge or accidental deletion can corrupt the dataset. Backups are stored in Azure Blob Storage associated with the capacity and can be restored to any workspace on the same capacity. Schedule automated weekly backups for production datasets as part of your disaster recovery plan.</p>
<h3>Capacity Right-Sizing</h3>
<p>Monitor the ratio of refresh duration to available refresh window. If your incremental refresh consumes more than 50% of the interval between scheduled refreshes, you are at risk of refresh queue buildup. Options include upgrading the capacity SKU, enabling <a href="/services/enterprise-deployment">autoscale with Fabric</a>, splitting the dataset across multiple workspaces, or optimizing the source queries to reduce per-partition refresh time.</p>
<h2>Next Steps: Enterprise Implementation</h2>
<p>Incremental refresh and data partitioning are foundational capabilities for any Power BI deployment serving more than a handful of users or managing more than a few hundred megabytes of data. The combination of properly configured RangeStart/RangeEnd parameters, verified query folding, optimized partition strategies, and XMLA-based automation transforms Power BI from a desktop analytics tool into an enterprise-grade data platform capable of handling billions of rows with sub-minute refresh cycles.</p>
<p>EPC Group has implemented these patterns across healthcare systems processing millions of patient records under HIPAA constraints, financial institutions managing real-time trading data under SOC 2 compliance, and government agencies with multi-year data retention mandates. Every engagement begins with a thorough assessment of your current dataset architecture, source system capabilities, and business requirements for data freshness.</p>
<p><strong>Ready to optimize your Power BI refresh performance?</strong> <a href="/contact">Contact EPC Group</a> for a free consultation. Our <a href="/services/data-analytics">data analytics</a> and <a href="/services/power-bi-architecture">Power BI architecture</a> teams will assess your current environment and deliver a partition strategy tailored to your data volumes, refresh requirements, and capacity budget.</p>
Frequently Asked Questions
What is the difference between incremental refresh and Direct Lake mode in Microsoft Fabric?
Incremental refresh partitions an Import-mode dataset by date and refreshes only recent partitions from the source database. Data is stored in the VertiPaq engine within the Power BI capacity. Direct Lake mode, available exclusively in Microsoft Fabric, reads Delta Parquet files directly from OneLake without importing data into VertiPaq at all. Direct Lake eliminates the need for refresh entirely because queries read live files. However, Direct Lake requires your data to be in a Fabric Lakehouse or Warehouse (Delta format), while incremental refresh works with any source that supports query folding (SQL Server, Azure SQL, Oracle, etc.). For organizations not yet on Fabric, incremental refresh remains the primary strategy for managing large datasets efficiently.
Can I use incremental refresh with Power BI Pro, or does it require Premium?
Basic incremental refresh works with Power BI Pro workspaces. You can configure RangeStart/RangeEnd parameters and define archive and incremental windows in Power BI Desktop, then publish to a Pro workspace. However, several advanced features require Premium Per User (PPU), Premium capacity (P SKUs), or Fabric capacity (F SKUs): hybrid tables with DirectQuery partitions, XMLA endpoint access for programmatic partition management, the Enhanced Refresh REST API, datasets exceeding 1GB, and Large Dataset Storage Format. For enterprise deployments where automation and real-time capabilities are required, Premium or Fabric is effectively mandatory.
How do I handle source data corrections that affect historical archived partitions?
Historical partitions outside the incremental window are never refreshed automatically. They are read-only once initially loaded. When source data is retroactively corrected, you have four options: (1) Temporarily expand the incremental window to cover the affected period, refresh, then revert the window size. (2) Use XMLA endpoint with Tabular Editor to manually refresh specific partitions by name. (3) Use the Enhanced Refresh API to trigger a targeted partition refresh programmatically. (4) As a last resort, trigger a full dataset refresh which reloads all partitions. Options 2 and 3 are preferred for production environments because they target only the affected partitions without disrupting the rest of the dataset. EPC Group builds automated correction workflows using Power Automate that detect source corrections and trigger targeted XMLA partition refreshes.
What is query folding and how do I verify it is working for incremental refresh?
Query folding is the translation of Power Query M code into native source queries (such as T-SQL for SQL Server). For incremental refresh, it means the RangeStart/RangeEnd date filter is converted into a SQL WHERE clause that executes on the database server, returning only the rows within the partition window. Without query folding, Power BI downloads the entire table and filters locally in memory, which completely negates the performance benefit. To verify: in Power Query Editor, right-click the step where the date filter is applied and look for View Native Query. If it is available and shows a SQL statement with date predicates, folding is working. If it is greyed out, the filter is not folding, usually because an earlier step (custom function, cross-source merge, or unsupported transformation) broke the folding chain. You must restructure the query so the date filter appears before any folding-breaking step.
How many partitions can a Power BI table have and what happens when I approach the limit?
Power BI supports a maximum of 10,000 partitions per table. With daily partition granularity, this limit is reached at approximately 27 years of data. Monthly partitions support over 800 years, so the limit is rarely a concern with monthly grain. If you are approaching the limit with daily partitions, use XMLA endpoint to merge older daily partitions into monthly or yearly partitions. For example, merge 365 daily partitions from 2020 into 12 monthly partitions or a single yearly partition. This reduces partition count without losing any data. You can also reduce the archive window to drop the oldest partitions entirely. Monitor partition count using the DMV query SELECT * FROM $SYSTEM.TMSCHEMA_PARTITIONS filtered by your table name.
What is the recommended incremental refresh configuration for healthcare datasets under HIPAA?
Healthcare datasets under HIPAA require balancing data freshness with audit trail integrity and minimum necessary access. EPC Group recommends: (1) Archive window of 7 years to meet HIPAA retention requirements for designated record sets. (2) Monthly partition granularity for the archive period with daily partitions for the current year to enable targeted corrections. (3) Incremental window of 90 days to accommodate claims adjudication cycles and retroactive corrections common in healthcare billing. (4) Enable Detect data changes using a LastModifiedDate column to minimize unnecessary data reprocessing. (5) Large Dataset Storage Format enabled from day one since healthcare fact tables frequently exceed 10GB. (6) XMLA endpoint locked down to specific service principals with audit logging enabled. (7) Row-level security implemented to ensure minimum necessary access per HIPAA requirements. Contact EPC Group for a HIPAA-compliant Power BI architecture assessment.
Can incremental refresh work with non-SQL sources like REST APIs, Excel, or SharePoint lists?
Incremental refresh technically requires only the RangeStart/RangeEnd parameters and a date filter, but query folding is critical for performance. REST APIs, Excel files, and SharePoint lists do not support query folding, so Power BI must download all data and filter locally. This means incremental refresh still partitions the data and avoids reprocessing unchanged historical partitions in storage, but each refresh of the incremental window downloads all source data rather than just the partition window. For small sources (under 100K rows), this is acceptable. For larger non-foldable sources, stage the data into a SQL database or Fabric Lakehouse first using Data Factory or Dataflows Gen2, then configure incremental refresh against the staged copy where query folding is fully supported. This staging pattern is standard in enterprise data architectures.