Trigger a Dataset / Dataflow Refresh using Power Automate / Beginners Guide to Power BI in 2021

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
this video i want to show you how you can trigger power bi data flows and data set that refreshes automatically using power automate we're gonna go through it step by step together but before that why i implemented it in the first place all of that and more so without further ado let's get started hi my name is fenan 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 since i've learned about data flows i've started to use them more often but recently i've encountered an issue that i sold using power ultimate and i want to explain you the problem first so here's a typical scenario that you might have when it comes to implementing a power bi solution using data flows this is the solution that i've created for them every day at six am they get a new file from the system which gets uploaded to a sharepoint folder at 6 30 a.m we have a data flow schedule refresh that picks up this data and refreshes our data flow this takes less than 30 minutes every day at 7 am the data set has a scheduled refresh so it refreshes the data based on the data coming from the data flows and and again it doesn't take more than 30 minutes for this to happen and at around the half seven in the morning new data is available in power bi report that is cleaned and transformed uh for reporting now this solution from end to end is fully automated but there are some potential issues that could arise with this so let's say that for example the data was updated late so as you can see instead of the data getting uploaded to sharepoint at 6 00 am it gets uploaded an hour later this means that the data flow schedule refresh still happens at half six it doesn't really matter if there is data in the sharepoint folder or not the schedule refresh will still run in the same schedule regardless which means that even though this refresh will run it will not capture the latest data that you have in the sharepoint which in turn the data set also will be outdated now here's another issue that you might have with this solution so because these schedule refreshes are hard schedules it means that it doesn't matter if the previous process is done its job or not uh it will still do its job at half six or at seven a.m regardless of the previous job so let's say here for example although the data is uploaded at 6am in the morning the data flow scheduled refresh is happened at half 6 which picked up the correct data it took an hour to finish for some reason maybe there's a lot of data or maybe there's a lot of traffic and it took an hour to finish which finished at half seven the data set schedule refresh still started at seven a.m which means that it would have picked up the old version of data from the data flow which means that the data sets will not have the latest data that it needs uh for reporting so we needed to find a smarter way to deal with this ideally for one event to trigger another and this is basically where power ultimate comes in the idea is to create two flows one flow which will check when new data is available in sharepoint sites or sharepoint folder which then refreshes the data flow in another flow that checks for when the data flow refresh completes to then trigger the refresh in the data sets this makes sure that the continuity of the actions are done on a step-by-step basis and the next step doesn't start until the previous one is complete so now let's have a look at implementing these solutions step by step together so to give you a bit of background i want to show you this workspace that i've created for today we have the workspace here with a couple of items here and if i look at the lineage view you'll see we have a data flow that is pulling data from our sharepoint folder and we have a data set that pulls data from the data flow which then in turn is being used by the grocery sales report and this is the sharepoint folder where the power bi data flows is pulling its data from so we have three folders here and what we want to do is we want to upload or when this new data goes into this this sharepoint folder we want the data flows to refresh and then when the data flow refresh completes its refresh for the data set to refresh itself as well and by the way if you don't know how to use data flows in power bi and how to implement it i've actually covered it in a separate video already so check it out if you haven't yet so anyway now we're going to go back to the sharepoint folder here and we're going to create a flow from here by clicking the automate button here power automates and create a flow so it's going to give you some templates that you can use so we're going to hit show more and we're gonna look for this template called complete uh here we go so when a new file is added in sharepoint complete a custom action this is what we need exactly click that and we load up that template so we hit continue so the first part here is the trigger that checks so you have to tell it where um where it should monitor activity from and we know that the folder is in the shared documents here annual grocery sales and now we need to add a custom step here so we're gonna hit new and from here we're going to type uh data flows so we're going to he click here refresh a data flow so now all we need to do is look for that one so i we know that it's in workspace it's in northwind and the data flow is called grocery sales so just to remind you this is the data flow that we're looking for this green one here in the northwind workspace so now we save that um and that's the first flow pretty much done so from here let's also create a second flow uh which actually is not that difficult either so we'll hit create from here we'll select automated cloud flow we'll just rename this refresh dataset let's say and for a trigger we want to trigger we want to monitor when a data flow refresh completes and basically what we want is if this data flow completes this refresh we want to refresh the data set in turn so we make sure that the data set only refreshes when the data flow refresh completes so from here we click workspace we look for northwind dataflow is grocery sales and then we create a custom step here we now look for power bi so here is what we need refresher data sets so now we need to look for northwind once again and now we need to reference the data sets now the grocery sales data sets if you just remind you this is what we're looking for so we want to refresh this data sets when this data flow finishes its job so let's go back here hit save and that's it so now you've created the two flows uh one that monitors the sharepoint folder and one that monitors the data flow let's have a look at testing this so we'll go back to our sharepoint folder here and we'll upload this file grocery sales 2021 into this folder and let's see how this works so now let's go back to our flows and let's see what's happened so let's look at the first trigger so here you can see that the run succeeded so it noticed that there is data in our sharepoint folder so now it's refreshed the data flow itself and to check that let's refresh here and you can see it's refreshing now the data flow and now it refreshed started refreshing actually not so long ago you can see here i took 31 seconds and it's completed so that should have triggered the dataset refresh so let's go back to our flows let's go to the second one and let's see if that got triggered so now we look back at our flow and it's also been triggered it took a while because we are on a free plan with power automates but you can see that it succeeded in refreshing the data sets and if you look back here you can see the refresh finished now if you look at the refresh history right here refresh history it's refreshed complete um pretty quickly about three seconds that's really it for this video i hope it helped you understand how easy it is to start using power automate to fill in the gaps with your power bi solution thanks for watching as usual give this video like if you found it useful give it a dislike if you didn't so i know to do better for next time ask your questions in the comment section box below so i can help you and you can help others if you enjoyed this video we have a patreon page where you can support the channel and get exclusive perks like early access demo files and credits at the end of these videos thanks for watching once again and see you in the next one bye
Info
Channel: Solutions Abroad
Views: 17,771
Rating: undefined out of 5
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, how to power bi, power bi automation, power bi automatic, power bi automatically, power bi when refresh complete, power bi refresh, power bi power automate, power bi dataflows, power bi dataflow refresh, dataflow refresh, power bi wait for new data, power bi monitor
Id: JFPXwxWAcLI
Channel Id: undefined
Length: 9min 46sec (586 seconds)
Published: Fri Aug 06 2021
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.