Incremental refresh in Power BI

Incremental refresh and real-time data for semantic models in Power BI provide efficient ways to handle dynamic data and improve model refresh performance. By automating partition creation and management, incremental refresh reduces the amount of data that needs to be refreshed and allows for the inclusion of real-time data.

Incremental refresh extends scheduled refresh operations by providing automated partition creation and management for semantic model tables that frequently load new and updated data. For most models, one or more tables contain transaction data that changes often and can grow exponentially, like a fact table in a relational or star database schema. An incremental refresh policy to partition the table, refreshing only the most recent import partitions, and optionally using another DirectQuery partition for real-time data can significantly reduce the amount of data that has to be refreshed. At the same time, this policy ensures that the latest changes at the data source are included in the query results.

Supported plans:

Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded models.

Getting the latest data in real time with DirectQuery is only supported for Power BI Premium, Premium per user, and Power BI Embedded models.

Supported data sources

Incremental refresh and real-time data work best for structured, relational data sources like SQL Database and Azure Synapse, but can also work for other data sources. In any case, your data source must support the following:

Date filtering: The data source must support some mechanism to filter data by date. For a relational source, this is typically a date column of the date/time or integer data type on the target table. The RangeStart and RangeEnd parameters, which must be the date/time data type, filter table data based on the date column. For date columns of integer surrogate keys in the form of yyyymmdd, you can create a function that converts the date/time value in the RangeStart and RangeEnd parameters to match the integer surrogate keys of the date column.

Open Power BI desktop

And load sample data using use sample data tab.

Click on load sample data

Click on transform data tab

Change data type of data column as data/time

Create parameters

In this task, you'll use Power Query Editor to create RangeStart and RangeEnd parameters with default values. The default values apply only when filtering the data to be loaded into the model in Power BI Desktop. The values you enter should include only a small amount of the most recent data from your data source. When published to the service, the incremental refresh policy overrides these time range values. That is, the policy creates windows of incoming data, one after another.

1. In Power BI Desktop, select Transform data on the Home ribbon to open Power Query Editor.
2. Select the Manage Parameters dropdown and then choose New Parameter.
3. In the Name field, enter RangeStart (case-sensitive). In the Type field, select Date/Time from the dropdown. In the Current Value field, enter a start date and time value.

4. Select New to create a second parameter named RangeEnd. In the Type field, select Date/Time, and then in the Current Value field enter an end date and time value. Select OK.

Now that you've defined the RangeStart and RangeEnd parameters, you'll filter the data to be loaded into the model based on those parameters.

In Power Query Editor, select the date column you want to filter on, and then choose the dropdown arrow > Date Filters > Custom Filter.

In Filter Rows, to specify the first condition, select is after or is after or equal to, then choose Parameter, and then choose RangeStart.

On the Home ribbon in Power Query Editor, select Close & Apply. Power Query loads data based on the filters defined by the RangeStart and RangeEnd parameters, and any other filters you've defined.

In the Table view, right-click a table in the Data pane and select Incremental refresh.

In Incremental refresh and real-time data > Select table, verify or select the table. The default value of the Select table listbox is the table you selected in the Table view.

Specify required settings:

Review your settings and then select Apply to complete the refresh policy. This step doesn't load data.