There are different reasons for why you want to update the Power BI semantic model incrementally.
- Maybe you want to update part of the model every hour
- or maybe it just take to long to update the model.
In this blog post, I will show you how to set up incremental refresh of a Power BI semantic model.
Before we get started, there is some limitations you need to know.
- When the model is uploaded to powerbi.com, you can’t download it anymore.
- You can only do it on a table that has a date column.
- You can not easy trigger a full load.
- The License. This will work with all licenses. There is no limitation there.
The changes need to made in power query, so open power query by clicking transform data as shown below.
Then you add 2 parameters, by clicking new parameter like on the image below.
The parameters need to have 2 specific names. RangeStart and RangeEnd. Make them date/time and type in some default values like here:
Now, click the column you want to do your incremental refresh on, and select the datetimne filter and click between as below.
In the prompt that comes up, select the 2 new created parameters like so
Your data will now be filtered by the default values you typed into the parameters. Don’t worry about that. That will change later in the setup. For now, just click close and apply, to return to the power BI desktop.
Here you can now right click the table you have added the parameters to, and click incremental refresh like this.
And now it is pretty straight forward to set up.
- Here you indicate for how long you want to store the data.
- Here you select what part of the data that need to be updated.
You can add incremental refresh to more than one table. You don’t have to create more parameters. You just use the 2 you already have created, and add them as a filter.
When you are done, you can just publish it, and schedule a refresh. The refresh will now work with the incremental refresh.
A major drawback of this, is that you can not easy do a full load of the data, if you for instance find out that some of the old data is changed. To do that, you have to use the rest api, or update the data in Power BI desktop, and publish again.
But that is it. I hope you will find it useful 🙂