What is Incremental Refresh in Power BI? | Incremental Refresh in Power BI

hello guys welcome to our channel today i'm gonna discuss with you incremental refresh in power bi what is it how to achieve it and how it can be useful for you so enough all the questions let's head over to my laptop [Music] incremental refresh is a powerful feature in power bi which helps you to only load the data which is necessary for example in our data warehouse we have millions of rows of data but we don't want to load all the data generally our data get refreshed you know uh in the last one week or last one months or maybe next next last three months so with the help of this feature we can only load that specific time period of data and the rest of the data as it is this would save us a lot of time in order to load the data in power bi you should also keep in mind that it works only on where there is a feature of power bi which is known as query folding is applicable otherwise it won't work and secondly your data should always be in the import mode otherwise you cannot apply the incremental policies on the power bi incremental refresh once you publish your file in the power base services you cannot download it you will get an error message so i'll show you over here in the demo how to do that and what are the limitations that you can come across why do we need it well the refreshes are faster refreshes are more reliable and resources consumption is reduced there are basically four steps to apply incremental refresh in power bi the very first is to define the filter parameters after that we are going to use the parameters to apply filter and third one would be define the incremental refresh policy while the last one would be to publish the file to the power bi services so let's see how it works the very first step is to define the parameters what you have to do you have to go in the transform tab where you can edit the power query first of all we have to choose the manage parameters create a new parameter and now over here in the name we can define that means range start and range end parameters we need to define so let's start with the first range start if you want to give some description you can give it over here otherwise no and since it's a dead time field so we are going to define date time and you can start suggested value can be any value and let's give the date uh i would say 1 1 2 0 1 8 and time also we need to define so we'll say 12 0 0 0 am click ok now we need to define another parameter that is range end now go again in the manage parameters new parameter range end again description is optional since it's a date time field we'll start date time any value and we can define um let's see yeah so it's gonna say but it's saying something over here you'll see enter a date time value so that means it's not a not the correct format so we have to choose the correct format so let's try this so you see the warning sign has been gone that means uh first we have to define the day then month that depends on the your local data setting in your pc or power bi desktop click ok now we have our range start and range n parameters after this we have to apply the filter so what we have to do will go in the fact sales table and we go at last we'll see our order date is there where we want to apply the filters when you have defined the new parameters you can apply the filter so what we can do we can go to the date column which is the order date where we want to restrict our data we'll go in the filters we'll click on the custom filter field over there and once this pops up over here we have to first define since when we want to start so we'll say over there is after or equal to then i have a parameter and then rain start so we can select over there and condition we have to also apply and we'll say is before so that select is before again the parameter and over here by default it would appear the first parameter but we have to give this range and select okay so now we have define our parameter in the table we have defined the two separate parameters range start range end and you can see that this table is empty there is no data in this range so what we can do let's clear it out and see since when the data is starting the data is starting from the 2010 and it goes till let me just check it goes approximately 2011 or so 2013 because 1000 number of rows are only in preview so i believe the data is not from 2018 so what we can do i'll just do over here and instead of 2018 i'll say 2012 and again you can see now you can see some data so what i'll do again i'll go there and define my parameter again quickly the same as i did before i went into custom so guys you need to remember since my data is bit old uh that is i'm using adventurebox 2019 data and it's i think you know 2014 or 2015 only so you have to always make sure like which range you want to apply initially and later you can change that too so click ok once you've done that close and apply it would take couple of seconds to load the data so i'm gonna show you how we can apply the incremental policy what we have to do we have to go there and the table where we have applied our parameters will go on there right click on this table and you can see over there incremental refresh you need to click over there once you click here you will see some options over there here is a information symbol as well once you have deployed the table in public services you won't be able to download it back from the power bi desktop because we have going to set up incremental reference so we won't be able to download it so what we have to do if we have to publish it again in the existing file we have to save somewhere and then we have to edit it and then we can republish again so over here first of all i need to switch it on but do remember that incremental refresh only works in the import mode this won't works in the directory mode so always keep in your mind if you will do that this one this button is not gonna be visible for you now click okay now we have to enter the value like how long data we want to upload so for example i click over here let's say five and say years quarter months up to you which you want to select and then it's giving you the second option refresh rows where the column order date is in the last so i'll say last 10 days that's all or you can change it to 20 days as well because you know when the data has been changed in your data set or you can also apply this detect change so it it can detect the change and you will see option over there only refresh data in past 20 days if the maximum value of the determinant changes so you can determine the changes due date order date and then only refresh um or only refresh complete days so the data for which the days are being completed the data has been loaded that you can apply over there so just click apply the first refresh operation in power bi service will load the historical data for the last five years that i just mentioned the subsequent refresh options are incremental and they will refresh the data that was changed in the last 20 days up to to the current date the incremental refreshes will also remove calendar years that are older than five years prior to the current date that means it won't load any data which is before the five years so let's just publish this report in our public services it's done now click on open the report and you will find over here that since we have set up the incremental refresh over here we won't be able to download this power bi file and the admin but still i won't be able to download it because i have set up the incremental refresh so you will see let's click on this one and you will find it says can't export the powerby desktop because we have set up the incremental refresh so you won't be able to ever download the file but if you need to make the changes so you can only make the changes in the file that you have saved on your desktop or somewhere in some storage of location and then you can again upload it so this is how incremental refresh works in power bi you

Channel: BI Consulting Pro

Views: 28,115

Keywords: incremental refresh in power bi, refresh in power bi, how to load data in power bi, dynamic page navigation in power bi, business intelligence, business analytics, analytics, power bi, powerbi, microsoft power bi, power bi desktop, bi consulting pro, power bi security roles, power bi tutorial for beginners, power bi tutorial advanced, powerbiservice, ajaykumar, running total in power bi, running total in dax, power bi service parameters, parameters in power bi, power bi consulting

Id: F9oYnq1omnA

Length: 11min 15sec (675 seconds)

Published: Sat Sep 05 2020