One of the most necessary features of any dashboarding tool is the ability to schedule timely data refreshes. This allows for the data to be updated in real time as well as on a periodic basis. However, Power BI stands out in this regard as they have taken this to the next level with incremental refreshes which allows Power BI to retrieve only new or modified data from a data source. This allows us to quickly refresh the data without having to update the entire dataset. 

In this blog post, we will explore this feature and also discuss how to set this up and the different refresh policies of Power BI. 

Why Use Incremental Refresh?

Incremental refresh only refreshes new data which means it reduces load time and processing time. This results in much faster report generation and overall more efficient dashboard performance. This is particularly handy for companies where real time data is involved. 

Have you ever refreshed an entire dataset and it kept on loading and fetching data for hours just because you didn’t know how to implement incremental refresh before? 

If so, keep reading to find out how to set up incremental refresh in Power BI.

Requirements for Incremental Refresh

Incremental refresh is applicable for all datasets as long as they have the following:

• Date Filtering: To enable incremental refresh in Power BI, we must make sure the dataset has a date column in either the date/time or integer data type.

• Single data source: All queries must come from a single data source.

• Query Folding: This means Power Query can generate a single query to get the source data.

Step by step guide: How to Set Up Incremental Refresh

First identify the parameters for the incremental refresh - these parameters include the data source, columns and refresh policy (the policy includes when to get the data - time based or query based).

Step 1

In Power Query, click on Manage Parameters under the Home tab and click on New Parameter as shown below.

Step 2

Set up a new parameter as RangeStart

Also set up another parameter in the same manner as RangeEnd which is the end date for the  data and ensure the type is in Date/Time format. The values you enter should include only a small amount of the most recent data from your data source. When published, these data are overridden by the incremental refresh policy.

Step 3

Set filters to the dataset. For this we have to go to the Date column, click on Date/Time Filter > Custom. Then filter to RangeStart and RangeEnd parameters in the dialogue box. This will filter the dataset for that specific timeframe.

Step 4

To set this up for a specific table or dataset, open Power BI and right click on the table name and click on Incremental Refresh.

This will bring us to the following dialogue box. 

Here you can choose the time frame to incrementally refresh the data.You also have other settings which you can use to customize the refresh further. 

Review the settings and options selected and make sure this is what you want. Once done, click on apply to complete the refresh policy. 

Publish the report and then go to Power BI Service where we will schedule the refresh for this report. 

Step 5

Schedule refresh in the Power BI Service to set up the desired time and frequency of the refresh. The first refresh might take a while as this will load both updated and historical data, however, subsequent refreshes should be much faster since the incremental refresh policy is already applied. 

Limitations of Incremental Refresh in Power BI

One of the major limitations of incremental refresh in Power BI is that it only works with certain data sources such as SQL Server, Oracle and MySQL. Moreover, it might not work with certain APIs or web services specially in the case that the data source has a complex relationship. 

Also remember that you cannot download the PBIX file from the service account once you have set up Incremental Refresh. This means that if you make any changes, you have to do it in the PBI Desktop file and then set up the incremental refresh from scratch once again. 

Conclusion

Incremental Refresh is an amazing feature that is a must know for anyone trying to create reports and dashboards in Power BI. This can help you save valuable time, create efficient reports and enable real time data updates. 

I hope this article works as a guideline on how to set up Incremental Refresh for your reports in Power BI. Remember that this is just the starting point - Power BI has many more amazing features that can make your life much easier by unlocking the full potential of Power BI.

So keep learning!

FAQs

What is the difference between Power BI full refresh and incremental refresh?

The key distinction between Power BI's incremental refresh and full refresh lies in their approach to updating data. A full refresh reloads the entire dataset, while an incremental refresh updates only the new or modified data since the previous refresh.

How many refreshes are allowed in Power BI per day?

By default, Power BI allows for up to 8 scheduled refreshes per day for Power BI Pro licenses. However, this can be increased with a Premium Capacity license and can go up to 48 refreshes per day.