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

[@OlivierTravers]

Kudos for showing the bumps in the road that we're likely to encounter in the real world - and how a regular human will react to these frustrations! - as opposed to a perfect demo that glosses over the warts.

[@GuyInACube]

Thank you for that! I thought for a little bit on whether to re-record that bit to get the mistake out. But I figured there was value in seeing we all stumble from time to time. And possibly add in some colorful language to boot :)

[@DreeKun]

@@GuyInACube Exactly, you nailed it!

[@vincenium]

@@GuyInACube Respect

[@johnfromireland7551]

@@GuyInACube Do you make your own videos? Do you add those animations yourself? Or do you have a video specialist do all your work for you? ;-)

[@RezaDorrani]

I don’t think anyone can explain this any better Fantastic !!!

[@GuyInACube]

Thank you! 👊

[@SolutionsAbroad]

Wow that Microsoft Flow refresh is really getting me excited!

[@combatter]

This was a great explanation, really helped me in setting things up. Clear instructions and easy to understand. Thank you for making this video!

[@GuyInACube]

Great to hear that! Thanks for watching 👊

[@JackOfTrades12]

Thanks Adam! This looks like an avenue to manage refresh schedules across many reports from a single location. I'm thinking about having table that contains all our dataset names along with schedule data.

[@solarpunk_]

Adam, I was interested in Flow kicking off a refresh, but you sucker punched me at the last minute - still limited to 8 refreshes a day. I am interested in creating a (close to) real-time stock report. Showing stock levels in our manufacturing facility in a graphical way. Ideally I want a minute-by-minute refresh and I suspect that it will be a data stream I need to create for this. I'd be interested in a deep dive into real-time data displaying in powerBI from you guys and I can only imagine there are more like me looking for a solution too. I also was laughing along with the bloopers. It's good to know even the experts feel the same pain I do every day learning a new skill like powerBI. You guys really help, thanks.

[@GuyInACube]

Great to hear. Yeah I wanted to leave the bloopers in. We definitely feel the pain. Interesting comment on the real time piece. Something to look into

[@BoxLaneProductions]

you're lucky ive just found out after building the whole thing that I've got to dump the whole project thanks to another software company staggering and partioning off features

[@Alex-ol4bv]

Are you using direct query to get the data? if yes, you might want to give this a try: bigintsolutions.com/2019/03/29/refresh-power-bi-report-every-min-and-show-on-a-tv/ (haven't tried this personally yet but I thought it might help in your case)

[@SHAli1474]

Great video, thanks. Is it possible to update/refresh only one table in the model (premium capacity)?

[@mrgreenteatime]

Second error in a row.. . Adam: "Wtf?!..." I'm sure we all here can feel you Adam, another day in the office :) Thanks for the info

[@GuyInACube]

hahaha yeah. I thought for a few if i wanted re re-record that to clean it up, but I thought - nah screw it. Good to show that we all make mistakes.

[@oscarrosero2357]

Hello, thanks for the video, I want to ask you if you can put a button in the report so that when you press it, the information is updated.

[@dmatthieu2]

Great video Adam! We're doing a POC with MSFT so its good to understand how this works

[@matinqaiser4658]

Hi folks, great video. I have a setup where a report is fed by a dataflow. I have scheduled the refresh of the dataflow to 09:00am. Obviously this does not refresh the Report itself. If I chose 09:00am as the refresh time for the report itself aswell, will the reort refresh wait for the dataflow refresh to finish first?

[@rayromych5534]

Thanks for great video! Got a question - is there any way to update dataset parameters without having to open it in pbi desktop and then publish it?

[@alisotoudeh7388]

Perfect Adam, You are the best. Where can I find a video educating refresh all datasets in power Bi by 1 click like what you mentioned for 1 dataset by power shell.

[@achyuthegde6844]

Refresh via flow uses the 'API refresh'. It is limited to 8 times a day, just the way scheduled refresh works in 'Pro' license.

[@Xplorica666]

I got an API endpoint URL as my source of data and created a flow in my PBI report for user to initiate the report refresh whenever they look into the report as the API url will pick data from SQL Db and pass python algorithm and store data in the form of excel sheet. But would like to check if there is any other efficient way to suffice the need

[@caseyrmartinez]

Another great video. Thanks. Question: if your report is using a data flow, do you have to schedule a refresh of both separately. What is best practice here? Thx

[@GuyInACube]

There are two things in play. A dataset refresh will just pull what is available in the dataflow. The dataflow refresh would be separate. So, you would need to manage both. This Microsoft Flow feature only covers the dataset. Not the dataflow. I think that may be worth a video :)

[@OlivierTravers]

@@GuyInACube that's worth doing, some people are confused by that and have expectations that one refresh somehow does it all. community.powerbi.com/t5/Service/Dataflow-vs-Dataset-refresh/td-p/572076

[@nickdoy5785]

@@OlivierTravers Great contribution Oliver. I'm in the process of doing exactly as you describe in your post. I'm trying to accomplish this all in Flow as I think its the way forward at the moment. That said, I'm using the custom REST API connector you point to in order to get access to both the dataflow refresh action within flow and the mail notification options. Since I'm using an azure app registration it doesn't require signing in so my question is where is the end point of the mail notification when I add {notifyOption: MailOnCompletion}? I would hope I can either assign a mailbox to the app registration somewhere in AD or manipulate the API body to specify an email address. Any thoughts guys? Also, great stuff Adam. Patrick and yourself are awesome and really helping enrich the Power BI community and I know I would be in the wilderness without you both so keep up the good work guys!

[@ashah2910]

Just what I was looking for...Microsoft Flow works is best suited for triggering data refreshes to generate Power BI reports

[@panojkumar951]

Hi Adam, need your help in understanding difference between schedule refresh, incremental refresh and page refresh

[@leonardoramos5036]

As always, an awesome video Adam. I’ve been waiting for this feature since April. I’m just a bit off that Dataflows are not included on the list that can be refreshed. I hope the Flow team includes this.

[@Minhtran-rt3gn]

HI Adam , Do you know how to schedule auto report from oracle to SharePoint , then use that data to build Power BI ?

[@AkhilDuvvuru]

Hey Adam - Can we refresh one single table/partition using Power Automate? I'm able to do the same using Analysis Services (XMLA script). But just want to check if we can do it using Power Automate so that I can schedule the same.

[@mohamedrafi8872]

Great thanks. Does Flow trigger monitoring puts pain on source sql server!?

[@Kats072307]

8:54 wouldn't Scheduled refresh flow, be more handy ?

[@jansiebrecht5582]

Hi Adam @GuyinaCube, this is pretty cool stuff! Can you recommend a refresh method so I can let the users of the Power Bi reports (may be up to 20 people) to trigger refreshes of the data set themselves. Many thanks and regards, Jan

[@aubreymckillop9156]

Thanks This was good! Love all of the videos both of you do! One quick question on number of refreshes for power BI Pro. You said 8 a day ..Is this scheduled only or 8 only no matter whether it is scheduled or on Demand?

[@GuyInACube]

It is just 8 refreshes a day regardless of where you do it.

[@aubreymckillop9156]

@@GuyInACube Thank you! so what happens if you have 8 scheduled and you do some on demand throughout the day do they just not run after 8?

[@michaels1813]

Hello, Excellent Video. Can you create a schedule refresh with OneDrive? Thanks, Michael

[@ashdam]

Is it possible to specify other "notification on failure" emails ? if yes, how?

[@n2datasolutions443]

Is there anyway to get more than 8 refresh, or direct query when using sharepoint lists?

[@kmuralikrishna1582]

Really sooper... I have a question please reply.. Assume that powerBI report is configured with a user(x) . Now that user is left from organization. When it try to refresh the report it will show an error (missing credentials) Is there any way to get all the reports which is having user connection issues...? Next is can we refresh reports switching with another user from Power Automate ..? Please reply

[@ankursrivastava8635]

Great Video! One question - I have set my data gateway to refresh Power BI reports in service, but sometime when schedule run / on demand refresh I am getting an error message "We reached the data gateway, but the gateway can't access the on-premises data source". I have checked my gateway in Power BI service it is up and running. I am using local installation of SQL server which is single data source for my Power BI report and data gateway is also installed on same machine. I am not able to figure out the issue.

[@daveconnors62]

Great - the flow/power automate from an SQL update is exactly what i need.

[@truevaluebusinessintellige6146]

Thanks for another great video. Is there a way to turn off scheduled cache refresh for a dataset that is using a SSAS live connection? We have found that the default hourly refresh causes havoc on our servers CPU so we have change the setting to weekly on the weekend but would prefer the ability to turn it off altogether. Thanks, Gary

[@sumitpandey2919]

Can we use the Rest api option for refreshing the on premise Power BI report server dataset ?

[@neel13191]

Great Video, This video could have included TMSL refresh directly from Management Studio! Flow option certainly has a lot of potential for triggers and I would love to see when Microsoft includes options in flow like refreshing a single table or partitions to facilitate custom incremental refresh!!!

[@sunnysgadgetreviews5886]

Is there a simple way to get list of all datasets from powerbi server to refresh them at once?

[@habidata]

Hey, it is a very great tutotial. I would like to ask how to connect/refresh data from data lake gen1. After I built the connection with data lake folder path in powerBI, the "combine" button did not show up. It shows "table" under content column.

[@spen2431]

As always.... fantastic. Even better to see you guys make the odd error. Visiting the UK anytime soon?

[@GuyInACube]

Appreciate that. Yeah I felt keeping the mistake in had value. We aren't perfect. The next possibility for a UK visit would be SQLBits. Not sure when that will be though.

[@abdulfaizanmohammed3896]

great post!! question, i've SharePoint list and Power BI pro not the premium capacity, can i still do unlimited refreshes when list is modified or just 8 per day?

[@AnamKhan-uj3uq]

You cannot have unlimited refreshes. With PRO license you can have max 8 refresh per day.

[@arnohoedelmans]

Thanx Adam! I think i recognize power bi helper on your taskbar. I am looking for tools to publish a report to multiple workspaces at once. What's your experience tips? thanx

[@techwithneri7]

Thank you for your educational videos. Am trying to fresh my power bi automatically using scheduled refresh but I don't have a personal gateways installed when I tried installing it didn't install, any know problem?

[@agostinhoferreira6868]

Logic Apps are similar to Flow and not so bonded to a single users. It's indeed a interesting way of refreshing (I'm using Azure Analysis Services models with Automation Accounts or Logic Apps that are triggered in the end of the Data Factory that runs the ETL).

[@GuyInACube]

Agreed. I don't use Logic Apps much, but saw it could do the same. very cool.

[@chandraprakashmatam672]

yes, Logic apps does same without delay and can be integrated in ETL.

[@alhassan-khan]

wonderful, so no need for a data gateway when using flow ?

[@mdhidayat5706]

Hi Adam, is there a way to schedule refresh an excel file (data connection to SQL) in sharepoint using Flow?

[@alecubudulecu]

That should work. Same as using excel on local machine - but use a UNC file path.

[@mdhidayat5706]

@@alecubudulecu no worries, today you can use Power Automate Desktop for this scenario.

[@denisw4018]

Hi Adam! How can I send notification through dataflow after refreshing dataset? Or maybe it could be another way for allert.. Now, when i add notiification as next step after refreshing - allert sends after refresh starts (but i need - after complete).Please help.

[@ayobamioyewo2101]

Great Stuff, thank you. But my Microsoft flow is not trigger. Any advise.

[@AmitKumar-wv4lk]

Hi Adam. Thanks for the video. Question - How to capture dataset refresh status code in Power Automate? I have created a dataflow using XMLA end point and wanted dataflow to refresh using Power Automate when PowerBI Dataset (XMLA end point) is complete. Dataflow should not refresh when dataset refresh is not complete.

[@fernalication]

Good stuff guys, I think it's already too soon but about the direct query mode and the new automatic page refresh? Love your videos, always helping to follow the subject and stay up to date. Keep it up!

[@lucasamorim670]

Hello Guy in a Cube, Exist an API that can be get if your dataflow execute with sucess ? I'm trying to do that with PowerShell, but this API only start the refresh. Thank you for you videos.

[@advaitkulkarni2187]

So through Schedule Refresh as shown in Demo 2, would the reports get refreshed as well at that instant or do I need to hit the Refresh button in PowerBI Desktop?

[@simonsinger7138]

How well would this options work for incremental refresh? What would be your recommendation?

[@muzahidali12]

4th one was much awaited. Love it

[@shabihulhasnain1164]

Hi Guys, thanks for so nice videos, which are really helpful. I have a quick question. "Number of refresh requests in last 24 hours exceeded limit". If you'd like to lift this limitation, you may have to buy a premium license(48 times per day) . Does this means we can refresh only 48 times per day and not more

[@TheLogydog]

yes. it seems nice for this feature without the limitation but 8/48 times per day??? alright, that is not usable.

[@Soulenergy31]

1:56 getting data from PBI file in SP, automatic refresh

[@rodalves271092]

Hi ! Great video , thanks ! I think I’m having exactly the same problema with credentials that you had, how did you fixed it ? I’m using a list and a library in SharePoint as dataset .

[@briangibson5981]

Me too! It would be nice to see exactly what those two problems were and how you fixed them. I'm currently having problems with a flow, 401 error that I think is due to some sort of credential error. I can't figure out if I have to add members to the connections or if I am exceeding the maximum refreshes allowed. This particular flow is refreshing a data set in a premium workspace.

[@randynos.5933]

I created a Power Automate Workflow (When a new email arrives -> Refresh a dataset) which would work but the Data Refresh API shares the same limitation (8 refreshes per day) with "Schedule Refresh" with a Pro license. After exceeding the limitation, the API call gets the response saying "Number of refresh requests in last 24 hours exceeded limit". So this Flow would work in a scenario where there is no scheduled refresh plan required and the user needs a refresh only on demand.

[@jlkd91726]

Thanks Adam. Great video. If we have a query parameter in an API call and when an user enter the API query parameters the API should refresh , could we achieve this using flow and Power BI? I am thinking whether I need power apps or not in between. So, let I have an API query which uses a parameter DATE and API get call is made based on the date entered. Issue is each time user entered the parameter, the API call needs to be made and get the relevant data. If I only use PowerBI then users have to go to dataset settings in PBI service, change the parameter and hit refresh, which is not relevant. Flow could be helpful here.

[@marlonmunozguevara7778]

Hi Adam !! Thanks for sharing. In addition, Where can I get those stickers ? 🤔

[@GuyInACube]

We usually have Power BI and Guy in a Cube stickers with us when we are at conferences and such. Other stickers were mostly picked up from conferences as well :|

[@marlonmunozguevara7778]

Guy in a Cube oh so sad. Anyway...your work are awesome. Thanks for sharing all the info with the PBI community 👍🏻

[@giri41]

hi Adam...great video. How to stop sharing report after sharing for few days. once we share the report, do we still have the power to stop the report?regardsGiri

[@renvaldasmackevicius7823]

Just remove user(s) from user list

[@giri41]

How please??

[@naveenprabhu4143]

Awesome Video! Made by day for sure. Was looking for the refresh solution using API. Question: Does the refresh API work if the data for Dashboard is hosted in HDFS?

[@joaquinmaverick82]

Hi, you have been able to use the api to do a refresh in real time?

[@AHMEDALDAFAAE1]

Thank you very much for that valuable information!!!

[@giannisnikolakis5693]

Hey Adam, since you used a view on the database, do you know if a Flow can be triggered when an existing column is removed, or it's name changes ? I am asking since, this always fails on the online update.

[@LandscapeInMotion]

How many API refreshes via Flow do you get per day? Is it 48 as well?

[@mx2727]

I followed your tutorial and it start tu refresh data, but i got an issue of "the credentials provided for the web source are invalid, do you think i need to map a cloud conection?

[@paulnandes2841]

Is there a way to use flow to download an excel file from a link to direct download a report that I receive by Email every day and store into a shapoint folder where we can store data sources (and have a scheduled refresh set)?

[@michellew5745]

Great video! Is there a way where my consumers can click refresh in the app and get refreshed data? Ideally I want them to be able to refresh when they want, instead of always asking the creator (me). Thanks for your help!!

[@khudammunir3060]

Data is very informatic and helpful. I have a question, one of my client they have only one user id subscribed for Power bi web service and are publishing reports there, and further circulate those reports using power bi app link. They wanted to create a button in power bi report, that any user who is using if hits that button, it triggers its data set to get refresh. Can't go to power bi premium yet, and they are just testing things would may subscribe more accounts later on. Any solution for now ?

[@Ethernet480]

So for a couple of my input documents, my team drops new copies of the excel files into a Sharepoint site. When I update the links to the new documents, I'm obtaining errors. Is there a way to automatically point to the latest and greatest file within a Sharepoint folder or does it have to be replaced with a file that has the same name, OR does the actual file have to be updated with the latest info? Thank you-

[@MrDavisv]

We have a power bi pro license. Is it 8 refreshes per app per day or per power bi license per day ?

[@GuyInACube]

It is 8 times per day - per dataset, for Pro and Free users. For Power BI Premium, it is 48 times per day - per dataset - within the UI and unlimited from the REST API.

[@Marcel-dt5du]

Does the 8x/day refresh limit apply only to scheduled refreshes or does it also take into account manual refreshes?

[@renvaldasmackevicius7823]

Manual not. API - yes. Note; Premium capacity does not have 48 times limit for API

[@venkataramanakorada5621]

Hi Adam, The Flow workflow seems triggering all data set refreshes and coming out as success without waiting for the refresh status. This is also not triggering the refresh in sequence. How do we refresh multiple dataset refresh sequentially using Flow?

[@efthimiosspiridopoulos9531]

Updating the dataset using Automate Adam means that Report is updated automatically as well?

[@shaileshchandrakude522]

Hey, I have created a Report and that report I did live pin to Dashboard; whenever my data changes the report is being refreshed but not the dashboard. what is the thing I am missing...

[@AbdelfatahAhmed]

I have a question regard the refresh is there any way that allow me when i refresh only the new created data will be updated in power bi not refresh all the data in this example I’m using sql as data source ?

[@DAngeloSilvestre]

Yoooo .... Sometimes a refresh process that typically lasts 10-15min gets some problem and doesn´t finish succesfully. In the meanwhile it that schedule refresh keeps the status as in progress for 2 hours and I cannot start an manual refresh while that scheduled refresh hasn´t finished. How can I proceed to manually stop a refresh that is currently running?

[@marianonrg]

Manual refresh when you have a table connected to a MS Access database, why is not working? I have all the sources in the cloud ( no need of a gateway). Both Access and Power Bi are 64 bit. The Access database engine solved the refresh issue in Desktop, but in Service I cannot refresh. Is this not supported? Thx for all you great vids

[@kapildadheech1]

where we can get rest API URL to trigger the refresh

[@elizabethtachjian8151]

Hi, Are you able to create a video on how to use microsoft flow to refresh an entity in a dataflow please?

[@Harilakl]

I want to refresh few tables only in Data set from data flows. Is it possible

[@viditsrivastava15]

Can you please show a sample illustration for Snowflake connection instead of SQL server ?

[@japneetsingh550]

Do we need a gateway to use Microsoft Flow to refresh the dataset from SQL? Also, would appreciate if you can elaborate on the error and how you fixed it :)

[@renvaldasmackevicius7823]

If SQL on prem - yes. If in Azure - not.

[@japneetsingh550]

@@renvaldasmackevicius7823 If we are using SQL server for our dataset in Power BI, we need On premises gateway setup?

[@renvaldasmackevicius7823]

@@japneetsingh550 Yes, but good news it is quite easy process to configure. Machine with GW just need internet access, you do not need open any inbound ports.

[@japneetsingh550]

@@renvaldasmackevicius7823 I was facing some issues while configuring the on premises gateway. Do you happen to have any quick links to videos/discussions which might help?

[@Leonardocn98]

Love your energy!

[@abell385]

UTC timezone on the service side > the local timezone set on the desktop side = why?

[@GuyInACube]

It is a pain point for sure. Best I can point you to is to vote up the items on ideas.powerbi.com.

[@mikem8915]

Keep up the good work guys.

[@manu053299]

Is there a way to escape 8 refresh limit?

[@sangeetagarwal4570]

Hi...I had a question, if we have the pro version and not premium, are we limited to 8 schedule refreshes or 8 refreshes overall which includes on demand as well?

[@GuyInACube]

8 refreshes total a day. Including on-demand. At least from my testing and understanding.

[@sangeetagarwal4570]

@@GuyInACube Thanks a lot for your prompt reply... I'm a fan of your channel...Both of you are amazing! Thank you for the wonderful Power BI quick videos

[@joaquinmaverick82]

Hi,great video!! Do you have more information about using the API? I need to do this: SQL Server (in premise, not in the cloud) -----Real Time update (1 to 5 minutes)----> PowerBI Dataset+Report I think there is a way using powershell? like a scheduled task I have a Power BI Pro account Regards!!

[@csekhar4155]

Hi Adam, Can you please provide the shell script for data refresh.

[@venkataramanakorada5621]

Hi Adam. Thanks for a nice demo. This is exactly what I am looking for. I have 3 questions. 1. During the demo once the workflow is not triggered and you have added one more record to the table and for the second record the workflow is triggered. What is the reason for this? Does this workflow is guaranteed to be triggered in production? 2. If we add a task to send email after the dataset refresh task, will it send the mail after the refresh is completed? Or is it just invoking the refresh and moving to next task? 3. Is it possible to capture the refresh failures and perform other tasks like sending an alert mail etc from Flow? Regards, Venkat

[@miragliag]

Awesome! Thanks Adam and Thanks to the Power BI Team. So with Pro we can exceed more then 8 refreshes.... 8 scheduled and others triggerd by Flow over time or based on other events... Correct?

[@GuyInACube]

Most welcome Giovanni! 👊 No, I did some testing. You get 8 refreshes per dataset per day - for Power BI Pro - Period. So, if i did a scheduled refresh, then an API refresh, and then a Microsoft Flow Refresh - that's 3 refreshes.

[@BoxLaneProductions]

@@GuyInACube argh why don't these companies just tell you the catches before you learn their product inside out, is it so you accidentally buy it??

[@ducpham1378]

@@GuyInACube is there any work around for this?

[@vitto_af]

Hi, How to get the URL API of my report? and what's the script complete in power shell, please!

[@ShivamKumar-px3py]

When I am using the same approach , my refresh ie being displayed as a normal one instead of On-demand. It results in max 8 refreshes in a day.

[@BoxLaneProductions]

I've built a Leaderboard where you can click on players for more info that needs to refresh as different times come in, acouple of times a minute, but it seems power bi can't bring in data very well and caps it? Is power point or excel better for that?

[@GuyInACube]

I wouldn't think PowerPoint would do anything for you in this case. Are you using DirectQuery?

[@Shahid-rs3xf]

My power bi desktop dataset data comes from Mysql, g-sheet, excel, google BQ. What's the best way to refresh it automatically. It takes over 1 hour when refresh manually.

[@fictionalshortstories]

Sir, when I am trying to refresh power bi data using workflow it is taking 2 hours time and data not getting refreshed. I am inserting data into an SQL table so upon inserting the flow to be triggered and power bi data refreshed. Anything wrong in my approach?

[@CaioAloe]

Is it possible to user power automate with power bi report server?

[@M-Cubed]

So from what I understand, all of these methods just update power bi online with the most recently refreshed data from power bi desktop. Is there any way to automatically refresh power bi desktop?

[@devallamahesh989]

Hey do you know how to refresh two different data sources , Actually i have two source one is sql and other is web connection i have made merge query by using both sources in desktop its getting refresh when i publish into service its not getting refresh and it is asking me to do rebuild the query this error coming

[@tomchambers2996]

Is it possible to Refresh an Excel Data set in Microsoft Excel, NOT Power BI? I've searched everywhere but can't seem to find it.

[@1yyymmmddd]

Thanks for your yet another great video. Sadly this is only available on Microsoft Cloud. This makes many clients shift to alternative analytics solutions.

[@rickhoebee]

hi I have a REST API and using the import option. When I try to schedule a refresh power bi tells me that my sources are not supporting refresh. Do you know why? Is it how I have configured the REST API or is the "Webconnector" never able to schedule a refresh?