Incremental Refresh and Refresh Optimization

What Is Incremental Refresh?

Incremental refresh updates only the relevant portion of a large dataset instead of repeatedly processing the entire historical dataset.

For example, a sales table may contain ten years of transactions but receive new records every day. Refreshing the entire table each time can be inefficient.

An incremental refresh policy can retain historical partitions and refresh a configured recent period, reducing the amount of data that must be processed during routine updates.

How Incremental Refresh Works

Power BI incremental refresh commonly uses two Power Query parameters:

• RangeStart
• RangeEnd

These parameters define the boundaries of a date or time range used to filter the source data.

An illustrative Power Query filter is:

Table.SelectRows(

Sales,

each [OrderDate] >= RangeStart

and [OrderDate] < RangeEnd

)

Here, Sales represents the previous query step, and OrderDate is the date or time column.

The parameter types and column types must be compatible. The filter must also be configured appropriately for the intended incremental refresh policy.

After the model is published, the Power BI Service can manage partitions according to that policy when the source, model, and licensing or capacity configuration support the feature.

When Should You Use Incremental Refresh?

Incremental refresh is useful when:

• Tables contain large volumes of historical records.
• New or changed records are concentrated in recent periods.
• Full refreshes take too long.
• The data source can support the required filtering efficiently.

Incremental refresh requires careful design. If older records can change, the refresh policy must account for those changes. Otherwise, updates outside the configured refresh window may not be reflected.

Improve Refresh Performance

Consider the following practices:

• Remove unnecessary columns and rows before loading data.
• Filter data as early as practical.
• Use appropriate data types.
• Avoid unnecessary transformations.
• Preserve query folding where possible.
• Optimize source database queries and indexes.
• Use incremental refresh for suitable large datasets.
• Schedule refreshes during appropriate periods.

Query folding allows Power Query to push supported transformations back to the data source. This can reduce the amount of data transferred and processed locally.

Not all transformations can fold, so verify the behavior for the specific connector and query.