Incremental Refresh in power bi Desktop and Service

Hello everyone. In this video, I'm going to show you how to implement incremental refresh in the Power BI Desktop

And of course, how we can truly refresh our data in the Power BI Service

So let's dive in. The incremental refresh allows you to refresh only the necessary portion of your data instead of the entire data set

So let's see how we can implement this feature in the Power BI Desktop

In our sample report, you can see we have some visualizations such as the card, now the date is spanned from the 1st of January 2015 to the 31st of December 2020

So we can see the total unit card, the total sales, the number of transactions, and if we can see the slicer, the number of transactions by year table

and then we have the clustered column chart visual for the total unit by product and of course total sales by payment type

This data actually came from an SQL Server database, so I can click on this transfer data and then when I click on this recent sources, I can see this is my SQL Server database

And of course this is the particular database, sales data, and this is the table that I brought in into the Power Query Editor

So I'm going to click on cancel. Now from the F transaction data, I actually just created two dimension tables, the D product and of course the D payment type

To implement the incremental refresh, we need to set up some parameters in the Power Query Editor

So now make sure your data do contain a date column and of course the date column must have a date time data type

This is very important for the incremental refresh to work. So I'm going to click on manage parameters and I want to create two parameters

Now based on the documentation, we have to use some keyword such as the range start and the range end

So for the parameter one, I'm going to call this one range start

And of course I have to specify the data type which must be date time data type

And of course I want to filter this data from the 1st of January 2018 to the 31st of December 2018

So I'm going to come to the current value. I'm going to type in 0101 2018

We mustn't forget about the hour, minute and the second. So I'm just going to type in 00 colon 00 colon 00

And of course just specify the parameter 2. So this is going to be the range end

And of course I want to choose the date time data type. So this is going to be the 31st of December 2018 000000 columns

So click OK. Now in this case we have the range start and of course we have the range end parameters

I'm going to come to the F transaction and of course I want to apply these parameters based on the data

So I'm going to click on this filter dropdown. I want to perform date time filters

I want to choose the between operation. Now in the filter rows dialog box, we just want to stick with the basics

So we want to keep rows where the date is after or equal to the parameter that is the range start

And is before or equal to the same parameter. This is going to be the range end. That's going to be the 1st of January 2018 to the 31st of December 2018

So click OK and then the filter is applied to the data

Now what I'm going to do is just go ahead and close and apply the changes to the data model

Now when the changes occur, see what happened to our data. There we go. So you can see the 1st of January 2018 to the 31st of December

And of course the total generate, sales and so on and so forth

I'm going to come to the F transaction and right click on the table. And we want to set up the incremental refresh in the Power BI Desktop

So in this case, I'm going to choose the same F transaction table. And I'm going to toggle on this incrementally refresh this table

So click on it. Now we'll archive data starting from now. And this is going to specify 5 and this is going to be 5 years before refresh date

And we want to incrementally refresh data starting from. Again, I'm going to just type in 5 years

Now you can see data imported from the 1st of January 2018 to the 31st of December 2018 will be inclusive

So that's fine. As you can see, data will be incrementally refreshed from the 1st of January 2019 to the 31st of December 2023

So that's fine. Now we have some couple of options such as choosing the latest data in real time with direct query

Now this is premium only. And we can even choose only refresh complete years and of course detect date changes, data changes

So that's fine. I'm just going to scroll down. Now we can see the review and apply. So this is going to be 5 years before refresh date archive

And then the 5 years before refresh date and the refresh date is going to be the incremental refresh

So click on apply. And that's done. So the next thing we need to do is to go ahead and publish the report to the Power BI service

So now, of course, you need to have the necessary licenses for you to achieve this

So I'm going to choose the publish. So I'm going to publish this to my data factory that do contain a premium capacity

So I'm going to choose this data factory workspace. And then let's see

And there we go. So you can see your file was published but disconnected. That's fine

I'm just going to open this up. Openincr.pbix in Power BI service

So let's see the contents. There we go. So you can see our report is published into the Power BI service

So we can see the 1st of January 2018 to 31st of December 2018

Okay. Now the next thing we need to do is to go back to the data factory workspace

and we want to choose the schedule refresh, which is the semantic model

So click on the schedule refresh. And of course, we have to specify the gateway and cloud connections

So I'm going to click on this. Now the data actually came from my SQL server

So I'm going to map to the connection, the gateway. So this is going to be abuela-db01

And then we have the semicolon sales data. That's the name of the database

So that's fine. Click on apply. So you can see your updates to the INRC gateway connections have been applied

That's called close that. I'm going to scroll down. Now I'm going to come to the refresh

Now this is where I'm going to now specify the refresh frequency

And of course the other stuff. So I'm going to toggle this on

Of course I can choose the frequency, maybe daily or weekly. I want to actually go with the daily

That's fine. And of course, because I'm actually in London, so I'm going to stick with the UTC, Dublin, Edinburgh, Lisbon, London

So that's fine. And I want to add the time. Now when you check the current time, the current time is 2.19 a.m. in the middle of the night

So I'm going to come here and choose 2. Let's refresh by 2.30 a.m

So by 2.30 a.m. all I have to do is just refresh and then the data is included

So 2.30 a.m. Click on apply. So you can see your updates to the INRC refresh schedule changes have been applied

So that's fine. So I'm just going to wait for 10 minutes until 2.30 and then we can see the refresh taking place

So let's wait and see. You can see the time is now 2.31

So let's just refresh this page and let's see the changes. Refresh

And there you go. So you can see we have the report from the 2018 to the last date, the 31st of December 2020

So this is basically how we can implement and truly refresh our data in the Power BI service

I hope you enjoyed this video. If you do, like, share and comment. Thank you and bye for now. Cheers