On-premise incremental refresh with Power BI Pro Dataflows

hi in this video we're going to talk about self referencing queries and how can we use them to create incremental refresh with the help of data flow so you must wonder why do we need this kind of incremental refresh when they already have power bi pro data sets incremental refresh well there are two reasons why first reason is because this technique works with data flows something that is still not incorporated with our bi Pro data sets and the second reason is because with this technique you can achieve more flexible ways of handling new data not only time relevant data but also ID relevant data like data from Facebook updates or data from weather appease etc so now without further ado let's see how can we create such an incremental refresh with our example [Music] or we get into a technical part let's just rehearse what does it mean to have a self referencing table so you can see that we have a new data that is entering inside of our main query and you can see that in second step the new data gets appended to the history table and we see that after the data is appended the sted same table becomes the history table what does it mean it means that we have a recursion and with each refresh the history table grows to accumulate both new data and the history data that was present in the moment of refresh so now that after we see what does it mean in theory let's see how can we get this with the use of a data flow for the purpose of demonstration we will use an Excel table that is situated on our local drive that means that we will need to use enterprise gateway if you don't know what gateway is you can search it online and see how to use it how to setup it and what are its benefits so now we have this simple table it has only three counts date segment value and we will the first step is to get this table inside of our data flow so we go to power bi go to workspaces and let's create a new workspace and we will call it data flow save it and we go to add data flows we will click on excel file and now we need our path we'll use the path of the excel file go to info copy path and place it here what we can see is that it's URL is a local locally based which means that you will need to use a gateway in order to operate with this file it already acknowledges that that I have a gateway installed and I'll also credentials if you will be using this for the first time you will need to set up credentials and the path correctly before you can go forward with the file ok let's click on the next wait a bit now we can see our excel and the objects inside of it we will use new data table and we'll go to transform data be sure to always define the column types so this is date this is text number and click on simcoe's we need to name a data flow let's call it incremental fresh and let's refresh it it will take half a minute so in the meantime let's do in progress so in the meantime let's go to our power bi desktop file and we'll go to get data and get data from power bi data flows we will select our newly created workspace and we will choose our hopeful it's reload it yes it's loaded we will choose our data phone at the moment we only need the step to get this quote the source code you could acquire it also in the online version into data flows but it tends to be slow so at the moment I think this is a faster solution so just copy the code and we go back to the data flows we go to edit entities and we add a new blank query and it pays the code in here really important at this step do not select to use a gateway this is a data flow you it's already online and it does not need a gateway to pro to operate if you select gateway here you will have an issue later on down the road with VD configuration so just leave it on none and click on next it will stay that it is a linked entity but we can disable load and it's allowed to have disabled linked entities inside of Pro Licence and let's call it history table we need to configure connection the connection will be it will actually be our account our power bi account so we just need to connect with our credentials and we will see our history inside of our table now the next step is crucial we need to get inside of new data and we need to somehow append the history data with each refresh to the newly added one how to achieve it let's go to advanced editor I'll put this new data to whole new data into a table variable called new next we need check which is the first date available in the new data why because we want to filter our history table so that it does not include mood so that does it not multiply new data and history data if the new data already has some other values this way we can be sure that there will be no duplicate of sales for example so let's add a mean date variable and we will just write okay next we need to add a table variable which will hold the filtered history data hold filtered history and now we'll go with history is the name of the query it's the name of this query date is the name of the date column in the history table and the last step is to combine these two Grammies okay exit step is the last step of the query if we've written everything correctly okay should be fine let's configure connection now we see that we cannot add an enterprise gateway we need it for this source that we cannot edit from here but what we can do we can edit from the options and project options and here we can select to use the gateway that we have installed after we confirm now we can see that it acknowledges our data source the data gateway and it also recognized that with we already included our file as with conditionals and any correction so we click on connect ok privacy can you if everything works well we will see our table and we can click on close and save so basically this is it with these free steps we've created an incremental table that gets refreshed that gets appended with it with new data on each refresh we need to refresh it now we'll wait again for a minute in the meantime let us go back to the power bi and let us load this data into a power bi desktop okay so now we will see that when the data is loaded hopefully yeah okay so that it contains 20 rows we will add this row to the table so you can see that we have two dates and it's appropriate waves now let's go to our excel file let's remove these two dates and let's add 26th and 27th ok let's save this excel file exit and now let's refresh again so with the dish the data flow should hold the old data node to date and append the new two dates in in the whole new data table we will wait for half a minute you can see that it's still in progress we have completed the first two refresh and now I think it should be already refreshed let's refresh again okay and now we can see that both the old today's dates are inside and we have new dates a pendant that was all for this video hope you enjoyed it if you have any questions please post it down below and if you like this video hit like and subscribe button to stay tuned for more see you in the next video bye [Music]

Channel: Exceed Learning

Views: 3,431

Keywords: PowerBI, Dataflows, PowerQuery, Incremental refresh, Self reference, Data analysis

Id: aAzSZbrICxE

Length: 13min 57sec (837 seconds)

Published: Fri May 15 2020