BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
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.
The idea behind this is simple. Suppose you've updated your data to incorporate February's sales:
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:
Choose to create two new parameters in Query Editor.
Set each up to be of type Date/Time:
Give each parameter a Date/Time data type (not Date), and a default value.
The RangeEnd parameter should be similar to the RangeStart one:
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:
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:
You can choose to reference a parameter value as shown above.
This should give this final 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:
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:
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:
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:
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:
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:
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:
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.