How to use INCREMENTAL REFRESH for Datasets (PRO) and Dataflows (PREMIUM) in Power BI

in this video i want to show you how you can set up incremental refresh in power bi from scratch i'm going to show you how you can do it in a power bi data set and also how you can do something similar with your power bi data flows all of that and more so without further ado let's get started hi my name is fernando and welcome to the solutions abroad youtube channel where i focus on teaching beginners the wonderful world that is power bi i upload new videos every week so make sure you hit that subscribe button and the bell icon to get notified when a new one is out so before we jump into the demo i want to explain what incremental is and what it's really used for so in one of my previous videos we covered how to set up scheduled refresh to automatically refresh your power bi reports and these schedule refreshes means that every time your report gets refreshed it takes all the data from your data source and replaces everything in your power bi data sets and in a very small power bi data set that we work with it's okay but when you have a data source that has a lot of data you don't want to be pulling all of those data every single time you want to refresh your reports incremental refresh allows you to keep historical data in your data set and only refreshing the data that is actually changing so let me show you how you can set it up in a power bi data set so here we're gonna start from scratch so what we're gonna do we're gonna connect to my sql database in my local server so we're gonna click sql server from here and it's the server is called localhost internally hit okay you'll see that we'll have some options here so we have the database that we want to use so we're going to pick one at random so let's say we want to take the orders table here just so that we can use it as an example let's hit load and let's load it into our data sets so you'll see now that loads that table orders table and we've got some information about orders essentially and what happens is every time you hit this refresh button or when you schedule a refresh for this data set what it does is it pulls all the orders within this orders table from the sql into this power bi data set which we don't want to do we want to set up an incremental refresh instead so let's have a go at that so let's right click here before i click on the table here you see you have an option to put and use incremental refresh so let's click there in this window there's a warning it says that we need to set up some parameters for incremental refresh and specifically we need to set up two specific parameters so let me show you how to do it it's pretty easy so let's go back let's go to power query i said transform data here and let's add two parameters here so let's create two we'll name this one range start and the second one range end these names need to be exactly like this because this is how the incremental refresh recognizes these parameters so just make sure that it's named exactly like this um so next is we need to make sure that the type is date time that's how the incremental refresh recognizes that these are both date time parameters that we can use and then the current value now the current value we can put here we can say the 1st of january but it can be anything because it will get overridden by the incremental refresh once it's all up and running so we'll do again we'll do next year so now you've got the parameters set up we hit okay so now you have these parameters that we can use in our query the next thing we want to make sure is we want to filter the table that we want to do the incremental refresh on to use these parameters so in this case you know you for your tables you want to be looking for something like modified dates which is typically what you would use to handle incremental refresh but in this case any date time fields will do so we'll use order date for now so we'll hit the arrow here and we'll do date time filters and equals so we'll make sure that is after or equal to we'll select the range start here and is before range end that's what we want to to have in our filters right so hit okay uh it will filter out everything but that's normal because my sample data set is going all the way from 1996. so if you want to see some data here we can just put the 1996 here so hit manage parameter and we'll put 1996 and you'll see it gets you that data from 1996 to 2022 let's hit close and load so now let's go back and set the incremental refresh again so right click incremental refresh and now we can set up the incremental refresh the warning is gone so we want to select the table that we want to apply the incremental refresh on select enable it here and from here you've got a couple of customization options for incremental refresh so the first option is to store rows where column order date is in the last and you can choose the range here this is the historical data that you want to keep in your power bi data set so let's say we want to keep the past 30 years of data because we want to cover up to 1996 or even before the second column here refresh rows where column order date is in the last and again you have the selection of uh different range values here this is the part where you can control what uh your data set or your data refresh covers so let's say we want to refresh data that is in the last 30 days right so what this means is that if there are any rows that have uh the ordered it updated in the last 30 days it will get refreshed um from its historical data so we've got a couple of check boxes here detect data changes just make sure that your incremental refresh only refreshes when a new data has been changed or modified and you can set which date time column you want to set it as so this would be your our order dates but you can choose whichever column date time column you want so the second tech box here only refresh complete days make sure that your incremental refresh only refreshes data where the days are complete so basically if you have multiple refreshes in a day for example you have multiple refreshes of data today it won't count today because today is not a complete day yet and it makes sure that when you're doing reporting you're only reporting on days that are completed so you can take this box too if you want so once that's done you just hit apply all and publish it into the service and that's incremental refresh done for your power bi data set one thing you need to bear in mind is that implementing incremental refresh on your data sets means that you aren't able to download this pbix file back into your desktop if you want to make changes to it so it means that if you want to make modifications to this report that does incremental refresh you need to preserve the power bi file in your desktop and we publish on top of this report onto the service so let's give it a try so let's let's hit publish here let's say incremental refresh i'll publish it in my workspace for now let's open this report so here we have the reports and if we try to download this now which you normally would have this button here you'll see that you won't be able to download it presumably because you have the parameters set up in the dataset to work with incremental refresh so just bear that in mind when working with incremental refresh however if you want to work with incremental refresh without being hampered by this limitation you can also apply incremental refresh on your power bi data flows so if you go into one of my workspaces here the north wind workspace we've already got a power bi data flows already set up here and i covered how to create and set up a power bi data flow in another video so if you want to know how to set it up check out that video but basically it acts as its own data source where your data sets can pull data from and from here you can set up the incremental refresh by clicking the data flow here you see the tables that you have in the data flow so here the tables in the grocery sales data flow is only one you need to make sure that your data flow has at least one column that is a data type of data that's basically what we're going to use for the incremental refresh now the button for the incremental refresh is here click here that will give you the same options as power bi desktop the only difference is that you don't have to set up parameters for this to work all you need to do is just turn it on make sure you have the date time column there it will show up here if you have one and then again here you can choose the historical period you want to keep so let's say we want to keep three months worth of data and we want to refresh rows in the past 30 days again we'll check all of these boxes once again and hit save so now you have incremental refresh set up for your power bi data flows so one thing to bear in mind when using incremental refresh in power bi data flows is that you need to have a workspace that have a premium capacity however bear in mind that this limitation only applies to incremental refresh in your power bi data flows this means that you can apply incremental refresh to your power bi data sets without needing a premium a pro will suffice which is great and that's really it for this video i hope it helped you understand how easy it is to start setting up incremental refresh to your power bi data sets and data flows leave a like in this video if it helped you it's the best way to let me know that you enjoy this type of content get in touch using the social media links that are included in the description box below and thank you so much for watching guys see you again on the next one

Channel: Solutions Abroad

Views: 5,965

Keywords: solutions abroad, power bi, powerbi, power bi tutorials, power bi for beginners, beginners guide to power bi, data analytics, dax, data modelling, data visualisation, business intelligence, power bi 2021, power bi incremental refresh, incremental refresh, how to use incremental refresh power bi, refresh power bi, refresh dataflow, incremental refresh dataflow, how to use incremental refresh, how to implement incremental refresh, historical data, power bi keep historical data

Id: fZVtlckIYvY

Length: 10min 52sec (652 seconds)

Published: Fri Jun 11 2021