Use Microsoft Flow, and other options, to refresh your Power BI dataset

yo I'm Sachs I'm a guy in a cube and in this video we are gonna look at the different ways that you can refresh a power bi data set including using Microsoft Flo stay tuned if you're finding us for the first time be sure to hit that subscribe button to stay up to date with all the videos from both Patrick and myself alright how do we refresh a power bi data set I've actually been seeing some questions online either on Twitter Facebook or just emails that are coming my way in terms of questions about how to actually refresh something or how certain aspects work and so I wanted to go through and just highlight the different ways that you can refresh including a new option from Microsoft flow that will allow you to actually trigger and then update that refresh so enough of all this talking let's dig into my laptop and see how this works we are looking at power bi and I am in a specific app workspace called refresh demo this is where everything's gonna be i've actually got four data sets currently in place so one-two-three-four we're gonna walk through all of these the first is refresh demo once let me go to the report and let's just run this and so we can see here that we've got date/time so this is the date/time that the data was refreshed inside a power bi inside the data set itself and then we can see how many rows are actually in the table we can see that this was updated last updated at 12:09 p.m. so what's interesting about this report is it's actually tied to a onedrive share and so if we look at let me go back to the data set or back to the workspace we're going to settings and we can see under advanced that this workspace is tied to the sales group onedrive area if we go look at my sales group we can see that I've got a power bi desktop file here called refresh demo onedrive now what I can do here is when I go to get data I go to files you can pull a power bi desktop file from onedrive or a SharePoint document library as well and when you do this it is then tied power bi is tied to that onedrive location it knows where it is and what will happen is is every hour or so it'll actually go to refresh from the power bi desktop file a lot of people get confused about this they think that Oh every hour it's gonna trigger a refresh and it's gonna refresh all the data in my power bi desktop file that's not what it does what it does is actually triggers and pulls whatever the current information is in that power bi desktop file it's going to bring that into power bi and so if you haven't actually opened the power bi desktop file and hit refresh nothing's gonna happen alright let's look at that real quick so let me go back I will open I've got these sync locally so we can see here I've got sales group documents general so I've synced the actual onedrive location or the SharePoint document library to my local hard drive and so when I open this file it's gonna go and get it from the cloud and bring it locally and we can see here that it was last updated at 12:00 or 9:00 p.m. which matches our report in the service so now if I go ahead and refresh this report and we'll actually see that it's 141 p.m. and this data is updated so now when I save this it's gonna actually save it back to that onedrive location and so if we come back here we can see that it last modified an hour ago if i refresh this page can now see that it was updated a second ago and then at some point in the future power bi is gonna pick this up from the onedrive share and then I will it will reflect the proper date in the report if I go back into the report it still shows 12:00 or 9:00 p.m. and one thing you can do to look here is if we go to the data set and we go to the settings of the data set on the schedule refresh or on the settings page we'll see refresh history we can see there's two buckets here one is scheduled one is onedrive it's highlighting onedrive and it's saying that it picked it up at 12:15 p.m. the last time it picked it up from onedrive you can actually see the separation between if I'm doing a normal refresh or if it's actually doing the Refresh off of the onedrive share or the SharePoint library so this is a way that you can get the data updated without actually using a gateway now the challenge that you saw here was that I had to manually refresh the power bi desktop file and then at some point power bi is going to pick up those changes and so you need to figure out a way to update power bi desktop file there are some third-party things that are out there power update from the power pivot Provost is one example of an item that you can use but this is just an example that you can do this without a gateway and get that data updated all right so the next one is report demo - this one shows that it was updated at 5:44 p.m. and what's interesting here is I've actually got what's referred to as schedule refresh setup on this so if we go to the data set for schedule or report a refresh demo - we can see here that it was last refreshed at 12:40 4:00 p.m. and it's next refresh is tomorrow at 12:30 p.m. so refreshed at 12:40 4:00 p.m. anything on the service side is going to be UTC time and so that is why we're seeing 544 p.m. even though it was really 1240 4 p.m. so if we go into scheduled refresh settings we can see here we're going to refresh history we can see that the scheduled refresh actually took place and it completed successfully so you can go ahead and setup scheduled refresh if your gateway and everything is set up you don't need a gateway if it's just cloud sources so that's something to consider I can come down to schedule refresh can turn it on and I can list the times that I want to go ahead and refresh this report the thing to note about scheduled refresh is if I'm in a premium backed workspace using premium capacity I can do this up to 48 times a day so every half hour if I'm not backed by premium capacity I'm limited to 8 times a day that I can refresh this so scheduled refresh is an absolute way that you can go ahead and refresh your data on a timely manner one thing you'll note also is that I have it set to 12:30 p.m. and you'll notice that it didn't trigger until 12:40 4 p.m. so you're not guaranteed to actually go at that time it'll go within 10 15 minutes of that window so be aware of that as well the other thing we can do here is if we go back to the report or sorry go back to the data set I have another option here where I can say just refresh it now so let's go and hit refresh now it's gonna go ahead and refresh and then you'll see that refreshed updated now to 1:45 p.m. if we go let's go back and take a look at refresh history we can see that I had an on demand now which was the refresh now and I also had a scheduled refresh that took place as well so two different ways that we can go ahead and actually refresh the data in our report all right so we've got one drive back files where it'll pick that up every hour we've got on-demand refresh we've got scheduled refresh what else is there let's move on to the programmers delight and that will be using the API or PowerShell to actually update the data set itself so we'll come back all right let's go to the report let's see what time it is this shows 734 a.m. so it hasn't been updated at all in a while so let's go back and look at the data set the data set also showing the last refresh time with 749 a.m. that's when the file was uploaded or the report was uploaded there's no schedule refresh setup on this an API access has no information here so I'm gonna do is head over to power show you can absolutely do this using REST API so you can call them natively there's also the c-sharp library where you can actually use that SDK to trigger the refresh as well in this case I'm actually gonna use the power bi powershell commandlets and call that REST API for this given data set and so we'll see here that I've got my URL and I've got my app workspace and my data set ID and what I'm gonna do is just go ahead and run this alright so I'm logged in so let me go highlight that line we'll go and run that line BAM all right it has triggered the refresh in the service just like that so now if we go back to power bi will go and see so we're interested in refresh demo 3 and we'll see here that the Refresh now is showing at 147 p.m. and if we come in and we look at our refresh history we can see that it was listed as an on-demand refresh and now if we go back to our report for a refresh demo 3 it now shows 6:47 p.m. remember that it's using UTC time so it was really at 147 p.m. so that matches what we did in our data itself so alright that's cool we got API we got PowerShell on demand scheduled refresh putting it in onedrive or a SharePoint library now we get to the new item which is using Microsoft flow to actually update the data and refresh that data set all right so let's actually go through and create this so I'm gonna go to Microsoft flow and I'm gonna create a new flow and I want to create an automated from blank and I'm just gonna call it PB i refresh and what I want to do is update from sequel server so it's going to do sequel and I have two items here I can update when or I can trigger so these are the triggers right so I can trigger when an item was modified or I can trigger when an item is created in this case I want to do it when a given value in a table gets updated so let's go ahead and choose when an item is modified we'll do create let's go and make sure we got the right connection setup and then the table that I'm interested in here is called flow update so let's talk about this table really quick let me jump over to management studio and I will go ahead and script out my flow update table there's a couple of caveats that I ran into first is we have to have an ID column in here that is a primary key this is if you want to actually track when an items created also you want to have a column in here that's using the timestamp or row version data type that's how it's going to trigger off of whether something was updated so that needs to be in place so this table does have both of those and what I'm gonna do here is what I'm just gonna update the process date column and when I update that column it should pick it up on the flow side should trigger and then update the refresh inside of power bi so let's finish creating the Microsoft flow so we've got our database piece here new step go choose search for power bi RBI there and then we're gonna refresh a data set I'm gonna choose my refresh demo workspace and we're gonna choose report demo for all right and then we're gonna save that it's saved let's go back yep all right we can see that's here nothing has been run so it's just sitting here waiting all right so let's go back to just so you keep me honest here we're gonna go back to our report we're gonna look at refresh demo for it's showing 734 am so again nothing was changed go to our dataset we can see here that it was last refreshed at 1206 p.m. that's when it was uploaded all right so let's go sequel there's two things I want to do here one is I've got a view here which is actually limiting to the hundred thousand rows so let me go ahead and get rid of that because I want to see both update so before the report showed the time and that there was a hundred thousand rows let's go and execute that that's gonna alter the view great and then what's actually going to trigger this is and go look at the time say 153 p.m. all right let's go and update the flow BAM alright this table is now updated so now the Microsoft flow is going to trigger just go back to flow and will refresh and it hasn't triggered yet what I found is this could take a few minutes before it actually triggers I think the most I've had to wait is about five minutes but let's see how long it takes to actually refresh it at the time right now I've got is 154 it was 153 when I made the update inside of management studio so let's see how long it takes a few minutes late at all right we refresh and we can see here that it updated were triggered at 156 p.m. so only a few minutes later now if we go back to power bi let's refresh this so we can look at the data set itself and we'll see now it says 156 p.m. there is a saying that the data source was missing credentials that's awesome that's we will do that again date it to 158 p.m. never a dull life and we'll wait a few minutes to see when it gets updated a few minutes late at all right again about three minutes after so now let's go back let's go the data set tab will refresh such still showing an error all right after fixing up a couple problems with the Gateway not being set up correctly let's go ahead and set this up everything's fixed now let's go and update the flow go and execute it BAM let's wait until this one actually gets triggered correctly on the flow side you need to log it in a few minutes late that trigger it off for the third time third time's a charm let's go back to power bi its refresh go to our data sets BAM look at that refreshed at 207 p.m. if we come in let's go into our refresh history see the first two failed because what can I say we all make mistakes and then for the third time we did refresh they were all on-demand this is good let's go back to our report port demo for and we can see now it's showing 707 p.m. and we've got four hundred forty six thousand rows I updated this data set using flow by updating the database table now where would that come in handy it may be that you have like some ETL process and maybe part of the ETL process it goes through and updates that table and then flows gonna pick that up and trigger the refresh it's one example a lot of examples I saw were of SharePoint like the SharePoint list being up using that so if an items created inside of a SharePoint list kick off a refresh on the data set that seems really excessive to me because there's gonna be a lot of updates there and if you're not backed by premium capacity you're limited to eight refreshes a day so that's gonna be problematic in my example where you know hey maybe it was an ETL process that updated a table and then flow goes and kicks off the power bi refresh you could easily add that step into your ETL flow as well or your orchestration that you're doing but maybe that's a different group that's doing that and they're not connected to power bi so this would be a great option for you to update based on that and then it refreshes the data set as the data warehouse processing is done so it's an interesting option for you just know that it's a tool in your tool belt and you can use that if the scenario fits basically anything that can trigger off of a flow can be used to go ahead and kick off that refresh for your data set lots of options alright I want to pass this off to you what'd you think was Microsoft flow an option for you now to actually refresh a data set which option do you use the most I'm betting scheduled refresh and where you're doing something outside of it that's not imported so let me know down in the comments below I want to hear if you liked this video be sure to hit that thumbs up button smash it if you so desire if it's your first time here hit that subscribe button and as always from both Patrick and myself thank you so much for watching keep being awesome and we'll see you in the next video

Channel: Guy in a Cube

Views: 132,999

Keywords: microsoft flow demo, microsoft flow examples, microsoft flow for beginners, microsoft flow power bi, microsoft flow power bi refresh, microsoft flow tutorials, learn microsoft flow, ms flow, onedrive, power bi, power bi dataset, power bi dataset refresh, power bi onedrive, power bi onedrive refresh, power bi rest api, power bi rest api refresh dataset, power bi tutorial, power bi tutorial for beginners, microsoft flow

Id: ClmZ-8p6x9g

Length: 15min 29sec (929 seconds)

Published: Thu Oct 24 2019

Reddit Comments

[u/tylesftw]

Using Microsoft Flow has been great for me, as somebody that does not have access to the raw data.

[u/mixedliquor]

Thanks for this. Understanding data sources in PBI through SharePoint is something that baffles me and this video might help get me in the right direction.

[u/irpugboss]

Yeah this was a nice surprise, love it.

[u/zzzzoooo]

Does it mean that we don't need Gateway to automatically refresh the datasets ?

[u/Pole420]

Cool, I've been wondering about how to tie the timing of refreshing a dataset to the completion of a stored proc updating a table. This sounds like a promising option considering I don't have the ability to use the API.

[u/Chauc3]

Does this mean I can get past the 8/day refresh limit by using flow to trigger a refresh every 30 mins or so?

[u/SLeepdepD]

Check out powershell cmdlets for Power bi