Incremental Refresh in Power BI

What is Incremental Refresh in Power BI?

Incremental Refresh in Power BI Desktop or Service is a feature that allows you to optimize data refresh processes for large datasets. Instead of refreshing all the data every time, Incremental Refresh enables you to update only the data that has changed or is new since the last refresh. Helps in reducing data refresh times and resource consumption, making it practical to work with large datasets.

Pre-requisites for Incremental Refresh in Power BI

Some of the key pre-requisites to implement Incremental Refresh in Power BI Desktop include:

• Power BI Premium, Premium Per User (PPU), Power BI Pro or Power BI Embedded datasets
• Your dataset must be structured, logically related data sources such as relational databases (e.g. SQL, Azure Synapse)
• Ensure that your dataset has a date column or pass date parameters to enable filtering.
• A Date or DateTime column in your data source that represents the “refresh key” to determine what data should be refreshed.
• Knowledge of M Query Language to create custom functions for data transformation.
• A data model with tables that are suitable for incremental refresh
• Time limits are applicable to Power BI Pro or Power BI Premium, depending on what you choose to use.
• If you are using incremental refresh in Power BI Service, ensure that the current date, time, and timezone are accurately updated.

How to Set Up Incremental Refresh?

Here’s an overview of the steps to configure incremental refresh in Power BI:

Step 1: Parameter Creation for Filter

• Select a data source in Power BI Desktop, choose Transform Data in the Home tab to open a Power Query Editor window.

• Navigate to Manage Parameters under the Home tab and then select New Parameter.

• Create two new parameters, RangeStart and RangeEnd, in the Manager Parameters window.

Step 2: Filter Implementation

In this step, filter conditions shall be applied to the dataset based on the parameters we have created in Step 1.

• In the Power Query window, select the column to apply the filter and change the Data Type to Date/Time.

• For the selected column, choose the Date/Time Filters in the dropdown menu and then Custom Filter.

• Provide the Parameter conditions in the Filter Rows window and then click on OK.

• Navigate to the Home tab in Power Query Editor and select the Close & Apply option.

Step 3: Define an incremental refresh policy

Navigate to the Data pane, right-click on the dataset, and choose the Incremental refresh option from the menu.

In the Incremental refresh and real-time data, provide the required information and then click on Apply.

Step 4: Save your model and publish it to the Power BI Service

Step 5: Apply dataset refresh

Note that incremental refresh in Power BI Pro is also supported. Refer to the latest documentation for any changes regarding the availability of Incremental Refresh in Power BI Pro.

Examples

In this section, we will demonstrate the incremental refresh configuration through different examples.

Example #1 – Incremental Refresh for Multiple Tables

In this example, we will configure the incremental refresh for Multiple Tables in Power BI Desktop using two datasets, i.e., US Holiday Dates and CountryLockdowndates.

US Holiday Dates contain the list of US holidays, whereas CountryLockdowndates contains the list of COVID-19 lockdown dates across different countries around the world.

To perform the incremental refresh for multiple tables, follow  the steps outlined below:

Step 1: Open Power BI Desktop, select Transform Data in the Home tab to open a Power Query Editor window.

Step 2: In the Power Query window, select the column to apply the filter and change the Data Type to Date/Time for both datasets.

Step 3: For each of these selected columns, choose the Date/Time Filters in the dropdown menu and then Between.

Step 4: Specify the filter conditions, choose Parameter, and then Parameter names from the dropdown. Click on OK.

You can also repeat the steps 3 and 4 for the other table.

Step 5: Navigate to the Home tab in Power Query Editor and select the Close & Apply option.

Step 6:

• Navigate to the Data pane.
• Right-click on the dataset.
• Choose the Incremental refresh option from the menu for each of the tables.

Step 7: Provide the relevant information in Incremental refresh and real-time data window and click on Apply.

Step 8: Click on Publish, select a destination, and then click on Select to publish the file.

Example #2 – Incremental Refresh for Dataflows or Datamarts

In this example, we will configure the incremental refresh for dataflows in Power BI. You can set the incremental refresh at the table level thereby allowing the dataflow to incrementally refresh the underlying tables.

To configure the incremental refresh for dataflows, follow the steps below

Step 1: Configure your table similar to any other table (refer to example 1)

Step 2: Select incremental refresh in the table view post-creation of dataflow.

Step 3: Provide the required details on the incremental refresh setting screen. Turn on the slider. You can additionally provide options such as Detect data changes and Only refresh complete as per your requirements.

Example #3 – Incremental Refresh with Custom Filter

In this example, we will configure the incremental refresh with a custom filter in Power BI Desktop using the US_Regional_Wholesale_Sales_Data dataset.

US_Regional_Wholesale_Sales_Data contains comprehensive insights into US regional sales data across different sales channels, including In-Store, Online, Distributor, and Wholesale.

To perform the incremental refresh with a custom filter, follow  the steps outlined below:

Step 1: Open Power BI Desktop, select Transform Data in the Home tab to open a Power Query Editor window.

Step 2: Navigate to Manage Parameters under the Home tab and then select New Parameter.

Step 3: Enter the relevant details, such as Name, Type, and Current Value, in the Manage Parameters window. Here, we have provided Name as RangeStart, Type as Date/Time, and Current Value as 31-05-2018 12:00:00 AM.

Step 4: Select the New Parameter option again in Manage Parameter and enter the relevant details to create another parameter. Here, we have created the RangeEnd parameter with Type as Date/Time and Current Value as 05/01/2018 12:00:00 AM.

Step 5: In the Power Query window, select the column to apply the filter and change the Data Type to Date/Time.

Step 6: For the selected column, choose the Date/Time Filters in the dropdown menu and then Custom Filter…

Step 7: Specify the filter conditions, choose Parameter, and then Parameter names from the dropdown.

Step 8: Navigate to the Home tab in Power Query Editor and select the Close & Apply option.

Step 9: Navigate to the Data pane, right-click on the dataset, and choose Incremental refresh from the menu.

Step 10: Provide the relevant information in Incremental refresh and real-time data screen and click on Apply to save the changes.

Step 11: Click on Publish, select a destination, and then click on Select to publish the file.

Once the publication is successful, you will receive a confirmation message.

Limitations

• Incremental Refresh is available only in Power BI Premium or Premium Per User.
• It’s only suitable for datasets with a clear date-based refresh key.
• You may experience challenges with complex data models and relationships.
• There is a limit on the number of partitions you can create for a table.
• Complex scenarios involving dependent tables can be challenging to configure.
• Once you have published the model to the Power BI Service:
  • You will not be able to publish it again from the Power BI Desktop. Also, any republishing of the model would eliminate the data and existing partitions in the dataset.
  • You won’t be able to download the underlying dataset back as a .pbix file to the Power BI Desktop.

Important Things to Note

• Always use secure and appropriate credentials when connecting to data sources.
• Be mindful of credential expiration and password policies to ensure data source connections remain valid.
• In Power BI Service, data source credentials can also be managed, allowing centralized control and updates.

Frequently Asked Questions (FAQs)

 

Recommended Articles

This has been a guide to Incremental Refresh in Power BI. Here we learn how to setup incremental refresh in power BI, with examples, limitations, and points to remember. You can learn more from the following articles –

• Power BI Developer
• Power BI Integration with Azure
• Power BI Query Dependencies