Incremental Refresh in Power BI: Handling Large Datasets Efficiently

Every Power BI implementation eventually hits the same wall: the dataset gets big enough that full refreshes take too long, consume too many resources, and exceed the time windows available before users need the data.

A transaction table with 50 million rows, refreshed completely every 4 hours, doesn't just take a long time — it wastes resources re-loading data that hasn't changed. Incremental refresh solves this by loading only new and changed records, while retaining the historical data that doesn't change. Done correctly, a dataset that previously took 3 hours to refresh can be brought current in under 10 minutes.

This guide covers incremental refresh in Power BI from first principles to advanced configuration — including the common mistakes that break implementations and the best practices that make them production-reliable.

Key Takeaways

• Incremental refresh partitions datasets by date, loading only recent data on each refresh cycle
• Requires a datetime column on the fact table and two Power Query parameters (RangeStart, RangeEnd)
• Historical data is retained in older partitions that are never re-queried after initial load
• Power BI Premium (or Fabric) is required for incremental refresh with more than 10 partitions
• Detect data changes option further reduces processing by only refreshing partitions where data changed
• Hybrid tables (combining import and DirectQuery) enable real-time data alongside historical import partitions
• Proper configuration requires understanding Power Query folding — non-foldable queries break incremental refresh
• Monitoring partition health via XMLA endpoint and third-party tools prevents silent failures

---

How Incremental Refresh Works

Understanding incremental refresh starts with understanding how Power BI partitions data.

In a standard import model, the entire dataset lives in a single partition. Every refresh replaces this partition completely. For small datasets, this is fine. For large tables, it creates the problems described above.

Incremental refresh splits the fact table into multiple partitions, each covering a specific date range:

• Partition 1: 2020-01-01 to 2020-12-31 (historical, never refreshed)
• Partition 2: 2021-01-01 to 2021-12-31 (historical, never refreshed)
• Partition 3: 2022-01-01 to 2022-12-31 (historical, never refreshed)
• Partition 4: 2023-01-01 to 2023-12-31 (historical, never refreshed)
• Partition 5: 2024-01-01 to 2024-12-31 (refreshed monthly)
• Partition 6: 2025-01-01 to 2025-03-31 (refreshed daily)
• Partition 7: 2025-04-01 to current (refreshed hourly or on demand)

On each scheduled refresh, only the most recent partitions (5, 6, and 7 in this example) are processed. The historical partitions remain intact from when they were first loaded. This means a refresh cycle processes only a fraction of the total data — dramatically reducing time, memory, and source system load.

---

Prerequisites and Requirements

Before configuring incremental refresh, verify these prerequisites are met:

Licensing: Incremental refresh is available in Power BI Pro (with limitations) and Power BI Premium/Fabric (full capability). With Pro, you can configure up to 10 refresh periods. Premium removes this limit and adds the "detect data changes" feature.

Datetime column: The fact table must have a datetime column (not a date key integer — must be an actual datetime type) that Power BI will use to partition the data. This is typically the transaction date, event timestamp, or created-at column.

Query folding: The Power Query query that loads the fact table must support query folding — the ability to translate Power Query transformation steps into a source query (SQL, etc.) that the source system executes. If query folding breaks, incremental refresh fails silently — it loads all data on every refresh, defeating the purpose.

Source system support: The source must support date-range filtering efficiently. A source table without an index on the datetime column will produce slow refreshes even with incremental refresh configured, because each partition refresh will do a full table scan to find records in the date range.

---

Step-by-Step Configuration

Step 1: Create the required Power Query parameters

In Power BI Desktop, open Power Query Editor. Go to Manage Parameters → New Parameter.

Create two parameters exactly as follows (names are case-sensitive and must match exactly):

These parameters must be of type Date/Time, not Date. They will be overridden by Power BI's refresh engine at runtime, but they need valid default values for development and testing.

Step 2: Filter the fact table using these parameters

In the Power Query Editor, select your fact table. Apply a filter on the datetime column using the parameters:

= Table.SelectRows(Source, each [TransactionDate] >= RangeStart and [TransactionDate] < RangeEnd)

This filtering step is critical: it must fold to the data source. To verify folding, right-click the last query step and check whether "View Native Query" is available. If it's grayed out, folding has broken — investigate which transformation steps above it are breaking the fold chain.

Step 3: Verify query folding

Query folding breaks most commonly because of:

• Custom functions that can't be translated to SQL
• Merging (joining) two queries where one or both don't fold
• Certain text transformation functions (Text.Upper, Text.PadStart)
• Using list operations (List.Contains)
• Adding an index column
• Certain type conversion operations

If folding breaks, refactor the query to push the problematic operations to a later step after the date filter — or perform the transformation in a view in the source database rather than in Power Query.

Step 4: Configure the incremental refresh policy

In Power BI Desktop, right-click the fact table in the Fields pane → Incremental Refresh.

The configuration options:

• Store rows in the last N calendar years/months/days: This defines the total historical window kept in the model. Data older than this is automatically removed from the model (dropped partitions).

• Only refresh rows in the last N calendar years/months/days: This defines the rolling window that is re-refreshed on each cycle. Data older than this window is treated as historical (immutable) and never refreshed again.

• Detect data changes: (Premium only) Uses a separate datetime column (typically a "last modified" column) to detect which historical partitions have changed data and only re-processes those partitions.

Example configuration for a transactional database with 5 years of history:

• Store rows in the last 5 years
• Only refresh rows in the last 3 days

This creates partitions covering 5 years of data, but only the past 3 days' partitions are refreshed on each cycle.

Step 5: Publish and validate

Publish the report to the Power BI service. The first refresh after publishing will take longer than subsequent refreshes — it loads all historical data and creates all partitions for the first time. This is expected.

---

Advanced Configuration: Detect Data Changes

The "Detect data changes" option in Premium adds another layer of efficiency. It works by querying a designated column (typically a last_modified_date column) to determine whether any records in a historical partition have been updated. Only partitions where data has actually changed are refreshed.

Without detect data changes: the 3-day rolling window is always refreshed, even if no data changed in the last 3 days.

With detect data changes: the refresh engine checks whether any records in the rolling window were modified before deciding whether to process each partition. If Monday's data was refreshed on Monday night and no records were modified since, Tuesday night's refresh skips the Monday partition.

This is particularly valuable for scenarios where:

• Source data is written once and rarely updated (immutable append-only events)
• The rolling window is large (e.g., 30 days) but most days have no changes
• Source system query capacity is constrained

The detect data changes column must be indexed in the source database — the refresh engine queries this column for every partition on every refresh cycle.

---

Hybrid Tables: Real-Time + Historical Data

Power BI Fabric/Premium introduces hybrid tables — a powerful combination of import mode (historical partitions) and DirectQuery mode (current-day data). This enables dashboards that show data updated to the current minute alongside historical import data.

In a hybrid table configuration:

• Historical partitions (yesterday and older) are in import mode — fast, cached, fully aggregatable
• The current-day partition is in DirectQuery mode — queries run live against the source database

The user experience is seamless — queries span both modes transparently. A query for "sales this week vs. last week" pulls yesterday's data from the import partition and today's data via DirectQuery, combining them into a single result.

Considerations for hybrid tables:

• DirectQuery performance depends entirely on source database performance — a slow database means slow current-day queries
• The DirectQuery partition doesn't benefit from import mode optimizations (no VertiPaq compression, no pre-aggregations)
• Requires a Premium or Fabric workspace

---

Monitoring Incremental Refresh Health

Incremental refresh failures are often silent — the model shows as "successfully refreshed" even if some partitions failed or fell back to full refresh. Monitoring is essential for production reliability.

XMLA endpoint inspection: Power BI Premium exposes an XMLA endpoint that tools like SQL Server Management Studio (SSMS), Tabular Editor, or Azure Analysis Services can connect to. From there, you can query the partition metadata to see the last refresh time for each partition and whether any partitions are in an error state.

Tabular Editor 2 (free): Connect to the Premium workspace via XMLA and inspect the partitions table in the model. Each partition shows its name, date range, last refresh timestamp, and state. This is the most practical tool for diagnosing incremental refresh issues.

Power BI Activity Log: The admin activity log records refresh operations, including which partitions were processed and any errors. Available via the Power BI REST API.

Common failure patterns:

---

Query Folding Verification in Practice

Query folding is the most common reason incremental refresh fails to deliver its promised performance gains. Here's how to diagnose and fix common folding breaks.

Test 1: View Native Query. After adding the RangeStart/RangeEnd filter step in Power Query, right-click the step. If "View Native Query" is available and shows a SQL query with a WHERE clause filtering the date range, folding is working.

Test 2: Check the generated SQL. The native query should contain something like:

WHERE [TransactionDate] >= @RangeStart AND [TransactionDate] < @RangeEnd

If the WHERE clause is missing, folding has broken and the filter is being applied in Power Query's engine after loading all data from the source.

Restoring folding: If a custom transformation broke folding, move it after the date filter step, or perform the transformation in a SQL view in the source database and connect Power BI to the view instead of the table.

---

Frequently Asked Questions

Does incremental refresh work with all data sources?

Incremental refresh works with any data source that supports query folding and date-range filtering, including SQL Server, Azure SQL, PostgreSQL, Snowflake, BigQuery, Azure Synapse, and Databricks. It does not work well with sources that don't support query folding (Excel files, flat CSV, some REST APIs) — in those cases, full refresh is still required. For non-foldable sources, staging data in a SQL database before Power BI connects is the recommended workaround.

What Power BI license is required for incremental refresh?

Incremental refresh is available in Power BI Pro (limited to 10 refresh periods), Power BI Premium Per Capacity, Power BI Premium Per User (PPU), and Microsoft Fabric capacities. The "detect data changes" feature and hybrid tables require Premium or Fabric. For most enterprise implementations with more than 10 historical partitions, Premium or Fabric is required.

How does incremental refresh handle late-arriving data?

Late-arriving data — records that arrive after their transaction date (for example, a December transaction that arrives in January's data extract) — is handled by setting the rolling refresh window wide enough to capture late arrivals. If data can arrive up to 7 days late, setting the rolling window to 14 days ensures late arrivals are captured when the relevant partition is re-refreshed. Alternatively, the detect data changes option with a last-modified column captures late arrivals regardless of the rolling window setting.

Can incremental refresh work on dimension tables, not just facts?

Incremental refresh is designed for large fact tables and requires a datetime filter column. Most dimension tables (products, customers, locations) don't have a suitable datetime partition column and are small enough that full refresh is appropriate. For slowly changing dimension tables that have grown large (customer tables with 50M+ rows), an alternative approach is to use SQL views in the source database to filter recently changed records and handle history retention in the database layer rather than Power BI.

How do I see which partitions exist in my incremental refresh model?

The easiest way is to connect Tabular Editor (free version 2) to your Power BI Premium workspace via the XMLA endpoint. Under Tables → [your table] → Partitions, you'll see all created partitions with their date ranges and last processed timestamps. SQL Server Management Studio (SSMS) also connects via XMLA and shows partition details in Object Explorer.

What happens if incremental refresh fails partway through?

If a refresh fails midway, Power BI retries the failed partitions. Partitions that completed successfully before the failure are not re-processed — only the failed partitions are retried. This retry behavior means incremental refresh is more resilient to transient source system outages than full refresh. If a partition consistently fails, the partition remains in its last successfully-loaded state while new partitions continue to be refreshed normally.

---

Next Steps

Incremental refresh is foundational for any Power BI implementation that handles large transactional datasets. Getting it right from the start — with proper query folding, appropriate rolling windows, and monitoring — prevents the performance problems that force expensive rearchitecting later.

ECOSIRE's Power BI performance optimization services include incremental refresh design and implementation for large-scale enterprise datasets. Contact us to assess your current refresh architecture and identify optimization opportunities.