Power BI Incremental Refresh: Large Dataset Performance at Scale

Power BI incremental refresh is one of the most impactful features available for datasets that are too large to fully reload on every refresh cycle. Without incremental refresh, every dataset refresh reads and reprocesses the complete history — a dataset with three years of daily transactions runs a full three-year query every time it refreshes, even if only yesterday's data changed. At scale, this means hour-long refresh windows, high source system load, and Premium capacity consumption that makes enterprise Power BI expensive to operate.

Incremental refresh solves this by partitioning the dataset by date, refreshing only the recent partitions where new data is expected, and leaving older historical partitions unchanged. Refresh times drop from hours to minutes. Source load drops proportionally. Premium capacity costs are reduced.

How Incremental Refresh Works

Power BI incremental refresh relies on date partitioning. The dataset is divided into date-based partitions: a configurable number of recent partitions (the incremental range, where new data is expected) and older partitions (the historical range, which are kept in place and not refreshed).

On each scheduled refresh:

1. Power BI refreshes only the partitions in the incremental range

2. Historical partitions are not touched

3. The partition boundaries are managed automatically

For a dataset with three years of data configured with a 30-day incremental range, a daily refresh updates only the last 30 days of data — not the three-year history. If the historical range is set to 3 years, the older data is available for queries but is never re-queried from the source.

The date partitioning is implemented transparently — Power BI handles the partition management. From the report user's perspective, the dataset behaves identically to a fully-loaded dataset.

Prerequisites and Configuration

Incremental refresh requires Power BI Premium or Premium Per User licencing. It is not available in the free or Pro tier.

**Setting up the date parameters.** Incremental refresh requires two reserved parameter names in the Power Query query: RangeStart and RangeEnd. These must be created as Date/Time parameters with exactly these names (Power BI recognises them by name). The source query is filtered using these parameters:

#"Filtered Rows" = Table.SelectRows(Source, each

[OrderDate] >= RangeStart and

[OrderDate] < RangeEnd

)

The filter must be foldable to the source — it must be translatable into a native query predicate (SQL WHERE clause, OData filter, etc.) rather than evaluated in Power Query. If the filter is not foldable, Power BI must read the full dataset to apply the filter, negating the incremental refresh benefit. Check that Table.SelectRows folds by inspecting the query plan in Power Query diagnostics.

**Configuring the policy.** In Power BI Desktop, right-click the table in the Fields pane (or via the Model view) and select Incremental refresh. Configure:

- **Archive data starting:** the total historical range to keep (e.g., 3 years)

- **Incrementally refresh data starting:** the window that is refreshed on each cycle (e.g., 30 days)

- **Detect data changes:** optionally specify a column to check for data changes — only partitions where this column has changed are refreshed

For most transactional datasets, a 30–90 day incremental range covers late-arriving data while keeping refresh scope minimal. Very high-volume sources may warrant a shorter incremental window (7–14 days); sources with significant data corrections going back months may need a longer window (60–90 days).

The Detect Data Changes Option

The optional "Detect data changes" setting adds a second optimisation layer. Rather than refreshing all partitions in the incremental range unconditionally, Power BI checks a specified column (typically a last_modified timestamp or a max row count) in each partition. Only partitions where this column value has changed since the last refresh are actually refreshed.

For datasets where most historical partitions in the incremental window are stable (no corrections to month-ago data), detect data changes eliminates redundant partition refreshes. For datasets with frequent corrections across the full incremental window, this optimisation provides less benefit.

The detect column must be a single column that changes predictably when data in the partition changes. A max(updated_at) per day per partition, or a row count per partition, both work. Avoid using a column that changes even when the underlying business data has not changed.

Large Dataset Mode

For datasets intended to hold more data than can fit in Premium capacity memory (hundreds of GB to multiple TB), Power BI Premium supports large dataset storage format. In large dataset mode, the dataset is stored on disk rather than fully in memory; queries load the relevant portions as needed.

Incremental refresh and large dataset mode work together: incremental refresh manages partition refresh scope; large dataset format manages memory constraints. For enterprise datasets where the full history runs to hundreds of gigabytes, this combination enables Power BI to serve as the semantic layer over very large historical datasets — not just a 1GB extract refreshed nightly.

Enabling large dataset storage: in the dataset settings in the Power BI Service, enable Large dataset storage format. Note that large datasets require Premium Per User or Premium capacity.

Hybrid Tables: Real-Time Data for Current Period

For scenarios requiring near-real-time data in Power BI — where a daily refresh is insufficient and users need current-day or current-hour data — hybrid tables extend incremental refresh with a DirectQuery partition for the most recent data.

In a hybrid table configuration:

- Historical partitions are Import mode (fast, in-memory, no source load at query time)

- The current period partition (today, or last N hours) is DirectQuery (live query to source, current data, higher latency)

Users querying historical periods get Import mode performance; users querying the current period get live data from the source, with the performance characteristics of a DirectQuery query. This is the closest Power BI gets to a zero-latency analytics experience without sacrificing historical performance.

Hybrid tables require Power BI Premium and a data source that supports DirectQuery. Not all connectors support hybrid tables.

Common Configuration Mistakes

**Non-foldable filter.** The most common incremental refresh failure. If the RangeStart/RangeEnd filter cannot fold to the source, Power BI reads the full table on every incremental refresh. Check query folding in Power Query before publishing.

**Wrong parameter data types.** RangeStart and RangeEnd must be exactly Date/Time type in Power Query, not Date. Type mismatches cause incremental refresh to fail silently or to not apply the filter correctly.

**Publishing without incremental refresh configured.** Incremental refresh configuration made in Power BI Desktop must be published to the Power BI Service to take effect. Local refreshes in Desktop always do a full load, regardless of incremental refresh settings — this can mask configuration errors that only appear after publishing.

**Ignoring source timezone handling.** If the source system stores timestamps in UTC and the Power BI environment is configured for a different timezone, date partition boundaries may not align with source data boundaries. Test boundary handling explicitly — a partition configured to cover "May 1 00:00 to May 2 00:00" in the Power BI dataset's timezone should cover the same window in the source.

**Setting incremental window too short for late-arriving data.** If source data arrives with a lag (e.g., sales are posted to the database 3 days after they occur), an incremental window shorter than the lag will miss recent records. Match the incremental window to the actual data arrival pattern.

For Power BI architecture including incremental refresh configuration, large dataset mode, and Premium capacity planning, our BI strategy consulting covers enterprise Power BI deployments — contact us to discuss your Power BI requirements.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.