How to use incremental refresh in Power BI

Power BI includes the ability to do incremental refresh. This allows power bi to incrementally download and append data to data sets. This can considerably increase the performance of Power BI and Power Query.

This technology can be applied to all odata feeds whether Xero, MYOB or others.

The following article describes the steps to use incremental refresh in Power BI.

Table of Contents

Overview

You can use incremental refresh in Power BI with any endpoints that can be filtered by dates. This includes any field of type Date, DateTime or DateTimeOffset.

However, in order for Power BI to detect that incremental refresh can be used, Power BI must be able to detect that is is avaialble.

Power BI does this by checking the power queries and seeing if specific parameters are defined: RangeStart and RangeEnd.

In addition, these parameters must be defined as DateTime. Using any other data type will not work.

Finally, these parameters must be applied to one or more power query. Applying it to DateTime fields is easy. Applying it to Date or DateTimeOffset fields will require you to edit the filter step and convert the parameters to the necessary data type. More on this later.

It is worth nothing that while incremental refresh is configured via Power BI on the desktop, it only works in Power BI on the cloud. Power BI (desktop) does not have the actual tech required to run it. Only Power BI (cloud) does.

For the official documentation from Microsoft, visit the Microsoft website.

Back to top

Step 1: Add the RangeStart and RangeEnd parameters

To define the parameters within Power Query, follow these steps. Once done, you will have create the RangeStart and RangeEnd parameters.

  1. Go to the Home tab and click Manage Parameters.
  2. On the Manage Parameters dialogue, click on the New button.
  3. In the Name field, enter RangeStart.
  4. Tick the Required checkbox
  5. In the Type field, select DateTime
  6. In the Current Value field, enter a default date.
  7. Repeat the process for the RangeEnd parameter.
Power BI Incremental Refresh Parameters

Back to top

Step 2: Apply a date filter using the RangeStart and RangeEnd parameters

Once the parameters are created, you will need to filter your data. The steps involved depend on the type of Data.

For DateTime columns

  1. Find the DateTime column and use the dropdown above the column.
  2. Select Date\Time Filters and then select Between…
  3. In the Filter Rows dialogue, change to Parameter for both options.
  4. Select RangeStart for the first option.
  5. Select RangeEnd for the second option.
  6. Click OK to close the dialogue.
Power BI Incremental Refresh Filter

The resulting formula will look something like this.

= Table.SelectRows(..., 
   each [Date] >= RangeStart and [Date] <= RangeEnd)

For Date columns

  1. Find the Date or DateTimeOffset column and use the dropdown above the column.
  2. Select Date\Time Filters and then select Between…
  3. In the Filter Rows dialogue, enter a dummy value for both options.
  4. Click OK to close the dialogue.
  5. Go to the Formula Bar in Power Query and replace the first date with Date.From(RangeStart).
  6. In the Formula Bar in Power Query and replace the second date with Date.From(RangeEnd).

The resulting formula will look something like this.

= Table.SelectRows(..., 
   each [Date] >= Date.From(RangeStart) and [Date] <= Date.From(RangeEnd)

For DateTimeOffset columns

  1. Find the Date or DateTimeOffset column and use the dropdown above the column.
  2. Select Date\Time Filters and then select Between…
  3. In the Filter Rows dialogue, enter a dummy value for both options.
  4. Click OK to close the dialogue.
  5. Go to the Formula Bar in Power Query and replace the first date with DateTime.AddZone(RangeStart, 0).
  6. In the Formula Bar in Power Query and replace the second date with DateTime.AddZone(RangeEnd, 0).

The resulting formula will look something like this.

= Table.SelectRows(..., 
   each [Date] >= DateTime.AddZone(RangeStart, 0) and [Date] <= DateTime.AddZone(RangeEnd, 0))

Back to top

Step 3: Configure the incremental refresh settings

Once the parameters have been configured and applied as filters, close and apply your changes in Power Query to return to Power BI.

You will now need to configure the incremental refresh settings. To do so, follow these steps.

  1. Right-click on your table and choose Incremental refresh.
  2. Under the 1. Select table dropdown, choose your table.
  3. Tick the option to incrementally refresh this table.
  4. Under 2. Set import and refresh ranges, first enter the length and period to use for archiving.
  5. Then enter the length and period to use for the incremental refresh.
  6. Under 3. Choose optional settings, tick or untick the option to only refresh complete days.
  7. Click Apply to apply and save your changes.
Power BI Incremental Refresh Settings

How do periods work

Both the archiving settings and the incremental refresh settings allow you to choose a period length. The possible values are:

  • Days
  • Months
  • Quarters
  • Years

These values controls how Power BI will archive data and how it will download the data.

What is the impact of the period on the archiving settings.

For the archiving settings, this control when Power BI will remove data. You may think that choosing 1 year or 12 months would produce the same result. But that is not the case. If you choose months, Power BI will always retain 12 months, but it will purge the older months. If you choose Years, it will retain 1 year of data (from January to December) but will purge the older years.

Behind the scenes Power BI will create partition to store this data. These partitions will be based on Days, Months, Quarters and Years. As time passes, it will merge partitions from one period type into another (from days into months, from months into quarters, etc.). And when it comes the time, it will purge an entire partition’s worth of data once it is no longer needed.

What is the impact of the period on the archiving settings.

For the incremental refresh settings, the periods control the range of dates it will generate to pull data. If you choose 90 Days, it will download data 1 day at time for 90 different days. If you choose 3 months, it will download data three times, using a date range that spans 1 month at a time. Given this, it is beneficial to try to use a few larger increments over many smaller increments.

Back to top

Step 4: Publish to the cloud and test

Once all options are configured, it is time to publish to the cloud and test.

Note that refreshing on your desktop will never use the incremental refresh settings. Instead, Power BI (desktop) will only ever use the default values entered in the parameters. This is why it is important to use meaningful defaults for testing on the desktop.

Back to top