Use Microsoft Flow, and other options, to refresh your Power BI dataset
Vložit
- čas přidán 10. 07. 2024
- Adam looks at your refresh options for Power BI datasets. Including Microsoft Flow!
powerbi.microsoft.com/en-us/b...
Guy in a Cube courses: guyinacu.be/courses
********
LET'S CONNECT!
********
-- / guyinacube
-- / awsaxton
-- / patrickdba
-- / guyinacube
-- / guyinacube
-- guyinacube.com
**Gear**
Check out my Tools page - guyinacube.com/tools/ - Věda a technologie
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.
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 :)
@@GuyInACube Exactly, you nailed it!
@@GuyInACube Respect
@@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? ;-)
Wow that Microsoft Flow refresh is really getting me excited!
I don’t think anyone can explain this any better
Fantastic !!!
Thank you! 👊
This was a great explanation, really helped me in setting things up. Clear instructions and easy to understand. Thank you for making this video!
Great to hear that! Thanks for watching 👊
4th one was much awaited. Love it
Great video Adam! We're doing a POC with MSFT so its good to understand how this works
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.
Love your energy!
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!
Thank you very much for that valuable information!!!
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.
Keep up the good work guys.
Great video, thanks.
Is it possible to update/refresh only one table in the model (premium capacity)?
Great - the flow/power automate from an SQL update is exactly what i need.
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?
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
Great thanks.
Does Flow trigger monitoring puts pain on source sql server!?
How well would this options work for incremental refresh? What would be your recommendation?
Great feature !!!
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?
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
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
Hi Adam, need your help in understanding difference between schedule refresh, incremental refresh and page refresh
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
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.
You made my day!
Oh... I like doing that. Glad it was helpful. 👊
Is it possible to specify other "notification on failure" emails ? if yes, how?
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.
Hello, with the flow approach, is that possible to update parameters before dataset refresh? Thanks.
As always.... fantastic. Even better to see you guys make the odd error. Visiting the UK anytime soon?
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.
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?
Hello, Excellent Video.
Can you create a schedule refresh with OneDrive?
Thanks,
Michael
Great video!!!
Thank you! Glad you liked it. 👊
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!!!
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.
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.
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.
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 .
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.
Can we use the Rest api option for refreshing the on premise Power BI report server dataset ?
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?
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?
Is there anyway to get more than 8 refresh, or direct query when using sharepoint lists?
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.
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
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.
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.
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 ?
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)?
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
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 :)
@@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
@@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!
Updating the dataset using Automate Adam means that Report is updated automatically as well?
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!!
Awesome thx..
Is there a simple way to get list of all datasets from powerbi server to refresh them at once?
Cool vid and functionality. The Power Platform is really coming together and I've wanted this update for ages! Such a shame the SQL trigger takes its time. I've found that frustrating in the past.
Great Stuff, thank you. But my Microsoft flow is not trigger. Any advise.
Refresh via flow uses the 'API refresh'. It is limited to 8 times a day, just the way scheduled refresh works in 'Pro' license.
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.
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
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
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)
Just what I was looking for...Microsoft Flow works is best suited for triggering data refreshes to generate Power BI reports
wonderful, so no need for a data gateway when using flow ?
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?
It is just 8 refreshes a day regardless of where you do it.
@@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?
where we can get rest API URL to trigger the refresh
HI Adam , Do you know how to schedule auto report from oracle to SharePoint , then use that data to build Power BI ?
Hi, Are you able to create a video on how to use microsoft flow to refresh an entity in a dataflow please?
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.
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?
Hi, you have been able to use the api to do a refresh in real time?
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?
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.
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
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-
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
Thanks for your yet another great video. Sadly this is only available on Microsoft Cloud. This makes many clients shift to alternative analytics solutions.
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.
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?
Is it possible to user power automate with power bi report server?
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.
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...
How many API refreshes via Flow do you get per day? Is it 48 as well?
Hi,
How to get the URL API of my report? and what's the script complete in power shell, please!
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.
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?
You cannot have unlimited refreshes. With PRO license you can have max 8 refresh per day.
My Scheduling Refresh always failed why? In your Refresh History's type was scheduled but in mine is nothing even I have scheduled refresh my dataset. I need help on this.
Millions thanks
You are very welcome.
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?
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
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?
Is there a way to escape 8 refresh limit?
Hi Adam,
Can you please provide the shell script for data refresh.
how to acknowledge if refresh was sucessful in the power BI side to power Automate
Hello There? When I try to create the flow in Power Automate using SQL Trigger, I'm getting the error "business connector shared power bi with non business connector shared sql". How to resolve the same?
Does this work in Power BI desktop?
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
Does the 8x/day refresh limit apply only to scheduled refreshes or does it also take into account manual refreshes?
Manual not. API - yes. Note; Premium capacity does not have 48 times limit for API
Can u tel me the any one , list of the refreshes names plz
Hi Adam, is there a way to schedule refresh an excel file (data connection to SQL) in sharepoint using Flow?
That should work. Same as using excel on local machine - but use a UNC file path.
@@alecubudulecu no worries, today you can use Power Automate Desktop for this scenario.
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
yes. it seems nice for this feature without the limitation but 8/48 times per day??? alright, that is not usable.
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?
I wouldn't think PowerPoint would do anything for you in this case. Are you using DirectQuery?
We are having an issue with users viewing the report (not in Power BI Desktop) refreshing the data from a Private Azure Database. It works fine from within Power BI Desktop, so I am hoping the PowerShell method will help us to resolve this issue. I am going to give it a try now, thank you.
Can we trigger the Power BI Rest api using Pyspark?
Hi, this flow should definitely work but doesn't. The only way around it is a recurrence trigger and that works - any ideas?
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.
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).
Agreed. I don't use Logic Apps much, but saw it could do the same. very cool.
yes, Logic apps does same without delay and can be integrated in ETL.