How to Setup Incremental Refresh in Power BI

Power BI is a powerful data visualization tool that helps businesses gain insights from their data.

One of the features of Power BI that makes it stand out is Incremental Refresh. Incremental Refresh allows Power BI to retrieve only new or modified data from a data source. It allows us to do that rather than refreshing the entire dataset.

In this blog post, we will explore why you should use Incremental Refresh. We will also explore how to set it up in Power BI. And finally, we will look at the different refresh policies available for subsequent refreshes.

Sections

• Introduction
• Why Use Incremental Refresh?
• Requirements for Incremental Refresh
• How to Set Up Incremental Refresh
  • Setting up Parameters
  • Setting up Incremental Refresh in Power BI Desktop
  • Setting up Incremental Refresh in Power BI Service
• The Limitations with Incremental Refresh
• References

Why Use Incremental Refresh?

In the data analytics world, we have all encountered the data refresh problem at least once.

Incremental Refresh is a useful feature that can save time, reduce resource usage, and enable real-time data updates. By only refreshing new data, it reduces the time it takes to load and process data.

This leads to faster report generation and better dashboard performance. This can be particularly useful in industries where real-time data is critical.

Incremental Refresh can store the historical data and only refresh the new data, making the refresh time quicker.

Requirements for Incremental Refresh

The Incremental Refresh feature is supported for all datasets. Premium workspace is required if you need to set up a refresh to get the data in real time with Direct Query.

• Date Filtering: To enable Incremental Refresh, make sure the table you’re using has a date column. This column should be in either the date/time or integer data type. Having a proper date column is essential for effective date filtering and optimizing the refresh process.
• Single data source: All the queries must be from a single source.
• Query Folding: This is the ability of a Power Query to generate a single query statement to retrieve and transform source data.

How to Set Up Incremental Refresh

Setting Up Parameters

Before setting up the Incremental Refresh feature, it is important to define the parameters for the Refresh. These parameters include the data source, the columns to use for identifying new data, and the refresh policy.

The refresh policy determines when to retrieve new data, and there are two options: time-based and query-based.

1. In Power Query, go to Manage Parameters under the Home tab and click on New Parameters.

2. Set up the new Parameter as RangeStart. Please note the Type must be in Date/Time format.

3. Set up another Parameter as RangeEnd. Again, please note the Type must be in Date/Time format.

4. Once Parameters are set up correctly, we need to apply the filter to the fact tables.

Go to the Date column (data type must be in Date/Time) in your table. Then, click on Date/Time Filter > Custom.

5. Filter to RangeStart and RangeEnd parameters in the dialogue box.

6. If your date column is not in the Date/Time data type, you can use a function for your Parameters to work for Incremental Refresh.

• To create a function, open a Blank Query under the New Source in Power Query.
• In the formula Bar of the new Query, enter the following: DateKey = (x as datetime) => Date.Year(x)*10000 + Date.Month(x)*100 + Date.Day(x)
• Now you will have to go to the filter step in your tables and change the M Code to add DateKey.

7. Save, Close & Apply.

Refresh Power BI Data Incrementally

1. To set this up for a specific table, open Power BI Desktop and right-click on the table.
   

2. The following dialogue box pops up and gives you the option to choose the time frame you want to achieve the data or incrementally refresh the data. You also have the option to choose settings.

Note: Getting the latest data in real time with a direct query can only be done in the Premium capacity.

3. The Review and Apply section in the dialogue box shows the archived data and the Incremental Refresh period.

4. Publish your report and go to your Power BI Service. We need to schedule the Refresh for this report.

Incremental Refresh in Power BI Service

1. Go to the dataset of your report. Then, click Scheduled Refresh and set up the desired time and frequency of the Refresh.

The Limitations with Incremental Refresh

While Incremental Refresh is a powerful feature, it does have its limitations.

One of the main limitations is that it only works with certain data sources, such as SQL Server, Oracle, and MySQL.

Additionally, it may not work well with data sources that have complex relationships or depend on external factors. Examples of these are APIs or web services.

Also, once you have set up Incremental Refresh, you cannot download the PBIX file from the service. If you need to make any changes, it must be done on the PBI Desktop, and Incremental Refresh must be set up again.

Conclusion

Setting up Incremental Refresh in Power BI can help you save time, reduce resource consumption, and enable real-time data updates. By following the steps outlined in this article, you can easily configure Incremental Refresh for your Power BI Desktop and Service reports.

But this is just the beginning! Power BI has many powerful features that can help you unlock the full potential of your data.

So what are you waiting for? Start using the power of Incremental Refresh and Power BI today!

To learn more about Power BI and how to get the most out of your reports, check out our website for related articles and videos.

If you need help with your Power BI implementation, our team at Iteration Insights is here to assist you. We offer a range of services, from data strategy to solution delivery, that can help you transform your data into actionable insights.

Contact us today to discover how we can help you unlock the full potential of Power BI!

References

Below are the references that were used in this article:

Microsoft: Incremental refresh and real-time data for datasets

Microsoft: Configure incremental refresh and real-time data