In this article, I explain how to set up an incremental refresh in Power BI, the benefits of apply this policy in your data models what are the requirements for it and the important considerations you have to consider.

Step-by-step instructions to configure Incremental refresh

Incremental refresh policies are defined in Power BI Desktop to help you to filter large datasets by using Power Query date/time parameters and to dynamically partition the data into ranges once published to the Power BI service.

Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded datasets. But the real-time DirectQuery partition is only supported for datasets in Premium capacities.

Benefits:

• Faster time refresh as is not necessary to refresh the entire dataset.
• Lower consumption of memory and other resources in both Power BI and data source systems because incremental refresh optimizes refresh operations at the partition level in the dataset.
• Reduces the amount of unnecessary historical data stored in your dataset.
• Minimize network problems because the queries to source data   return quickly the query output.
• Able to work with large data models in Power BI Desktop without being limited by the memory resources available on your desktop computer.

Requirements to Configure Incremental Refresh:

• The column used to filter the table must be a date/time or integer data type.
• To the incremental refresh works the data source should support query folding because the partition filters must be pushed to the source system when queries are submitted for refresh operations. Otherwise, Power BI will show the following warning:

Steps

1. Create the RangeStart and RangeEnd parameters: These are used to filter the number of rows initially loaded into the Power BI Desktop model that have a date/time within the specified period.

In the Power Query Editor, select Manage Parameters:

Create RangeEnd Parameter:

• In Name type RangeEnd (case sentitive).
• Check as Required
• In Type, select Date/Time
• In Current Valueenter a end date/time value respectively.

Note: When the dataset is published the parameter values are overridden automatically by the Power BI service for each partition. There’s no need to set them in dataset settings in the service.

1. Filter the data using RangeStart and RangeEnd parameters. In Power Query Editor, Selecting the Custom Filtermenu option for the date column you want to filter on:

In the first condition select is after or is after or equal to, select Parameter:

In the second condition, select is before and and then select RangeEnd:

Important:

• If you selected is afterin the first condition, then select is before or equal to
• If you selected is after or equal toin the first condition, then select is before for the second one.

Other filter combinations may result in double counting of rows.

Click ok to close, and then click Close & Apply.

3. Define the Incremental refresh policy: In data view, right click on the table and then click on “Incremental Refresh”:

4. Specify required settings:

Review your settings and then click Apply. Source data is not loaded with this step.

5. Save and publish to the service: Once the above steps have been completed, save your model, and publish it to the service.

If your dataset is going to be large, be sure to enable Large dataset storage format before to invoking the first refresh in the service.

6. Refresh dataset: You can now refresh the model. The first refresh may take longer to import the historical data. Following refreshes will be much faster because only data for the period specified in the refresh policy setting will be refreshed.

Important considerations

– Filter date column updates: In case of a deletion in the historical range and not the incremental range it will not be detected. This could cause data refresh failures due to partition-key conflicts.

– Refresh policies are defined in Power BI Desktop, and they are applied by refresh operations in the service.

– Be careful when you make changes in the original Power BI files because if you republish it, you could remove any of the existing partitions. In this case you must deploy only the metadata using tools as ALM Toolkit or Tabular by using Tabular Model Scripting Language (TMSL). Check: Metadata-only deployment using ALM Toolkit. Apply to Power BI premium.

– Once a PBIX file with an incremental-refresh policy is published in Power BI service, you cannot download the dataset back and open it in Power BI Desktop.