COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
The February 2020 update only contains one big change - incremental refresh
Part two of a three-part series of blogs

The February 2020 Power BI update only really introduces one new feature - the ability to partition your data so that Power BI only needs to refresh the most recent changes.

  1. Power BI - February 2020 updates
  2. Incremental Refresh (this blog)
  3. Things waiting in preview

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Andy Brown on 29 February 2020

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Incremental Refresh

The idea behind this is simple.  Suppose you've updated your data to incorporate February's sales:

February sales waiting to be imported

February sales are waiting to be loaded into Power BI Service.

However, you want to avoid Power BI Service having to do a full refresh, when you know that only the most recent month's sales have changed.  The way round this is to set up incremental refresh.

The obvious benefits of incremental refresh is that you'll be uploading far less data, so refreshing data will be quicker and less liable to fail because a connection times out.

Creating the necessary parameters

The first thing you have to do is to create two parameters with the special (and case-sensitive) names RangeStart and RangeEnd.  Start by creating parameters in Query Editor:

Creating parameters

Choose to create two new parameters in Query Editor.

Set each up to be of type Date/Time:

Date/time parameters

Give each parameter a Date/Time data type (not Date), and a default value.

The RangeEnd parameter should be similar to the RangeStart one:

End of range parameter

The parameter for the end of the range.

Now filter your data so that it lies between the two parameters specified, using a field of type Date/Time:

Applying a filter

It would be tempting to use Between..., but Microsoft are very specific in the sort of date filter you should create.

Filter your data so that it lies on or after the first date, and before the second:

Filtering by parameters

You can choose to reference a parameter value as shown above.

This should give this final choice:

Final filter choice

You can apply your filter and close down Query Editor to return to Power BI.

Setting incremental refresh policies

Back in Power BI, you can choose how you want incremental refresh to work:

Incremental refresh

Right-click on the relevant table in Power BI, and choose Incremental refresh from the context menu which appears.

 

You may now get the following error message:

Non-foldable query error message

This message means that the data source I'm using (in this case an Excel workbook) may not let Power BI push filter values down to it.

My solution to this was to change my data source to a SQL Server database.  SQL Server does support query folding, so the above message then disappears to give this:

Incremental refresh dialog box

See below for what these options mean!

Here's what the above options mean.  Firstly, I've chosen to limit Power BI to refreshing my Purchase table only when needed:

Incremental refresh enabled

I've enabled incremental refresh on the table of purchases.

 

I'm going to keep any purchases made in the last 5 years, but only refresh data from the last quarter:

Store and refresh rows

You can choose how much data you keep, and how much you refresh.

A consequence of this is that Power BI won't be aware of any data which you delete before the last quarter.  Make sure that changes only occur in the last period you've specified for row refresh.

You can also specify a column to monitor, to see whether a refresh is needed:

Detect data changes

If you tick this box, Power BI will only refresh data if you've added a row with a payment date greater than any other already in the table. Note that you can't use the PurchaseDateTime column here, since that's being used to control your incremental refresh policy.

Finally, you can specify that if a refresh happens, it should replace all of the data for a given time period:

Refresh complete period

Sometimes you will want to ensure that you refresh all of the data for a day, month or quarter.

Publishing your report

You can now publish your report.  When you refresh its data, Power BI will only refresh the last period's worth of data.

Users of Analysis Services (Tabular Model) will recognise that Power BI is creating partitions of the table in question, and only processing data for the most recent partitiion.

This blog has 0 threads Add post