Use Microsoft Flow, and other options, to refresh your Power BI dataset
Вставка
- Опубліковано 5 сер 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/ - Наука та технологія
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? ;-)
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 👊
Great video Adam! We're doing a POC with MSFT so its good to understand how this works
Wow that Microsoft Flow refresh is really getting me excited!
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.
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.
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!
4th one was much awaited. Love it
I don’t think anyone can explain this any better
Fantastic !!!
Thank you! 👊
Thank you very much for that valuable information!!!
Keep up the good work guys.
Great - the flow/power automate from an SQL update is exactly what i need.
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!!!
Love your energy!
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.
Great feature !!!
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
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?
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.
How well would this options work for incremental refresh? What would be your recommendation?
Thanks for your yet another great video. Sadly this is only available on Microsoft Cloud. This makes many clients shift to alternative analytics solutions.
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.
Great video, thanks.
Is it possible to update/refresh only one table in the model (premium capacity)?
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
Great thanks.
Does Flow trigger monitoring puts pain on source sql server!?
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.
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?
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.
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
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)
You made my day!
Oh... I like doing that. Glad it was helpful. 👊
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?
Great video!!!
Thank you! Glad you liked it. 👊
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 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
Is it possible to specify other "notification on failure" emails ? if yes, how?
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)?
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?
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 ?
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.
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.
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?
Is there anyway to get more than 8 refresh, or direct query when using sharepoint lists?
HI Adam , Do you know how to schedule auto report from oracle to SharePoint , then use that data to build Power BI ?
Can we use the Rest api option for refreshing the on premise Power BI report server dataset ?
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.
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!!
Just what I was looking for...Microsoft Flow works is best suited for triggering data refreshes to generate Power BI reports
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?
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.
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.
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-
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?
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.
Hi Adam, need your help in understanding difference between schedule refresh, incremental refresh and page refresh
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.
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.
Updating the dataset using Automate Adam means that Report is updated automatically as well?
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.
Hi,
How to get the URL API of my report? and what's the script complete in power shell, please!
Hello, Excellent Video.
Can you create a schedule refresh with OneDrive?
Thanks,
Michael
How many API refreshes via Flow do you get per day? Is it 48 as well?
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?
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 ! 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.
Is there a simple way to get list of all datasets from powerbi server to refresh them at once?
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
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.
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.
Awesome thx..
Hi, Are you able to create a video on how to use microsoft flow to refresh an entity in a dataflow please?
Great Stuff, thank you. But my Microsoft flow is not trigger. Any advise.
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.
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!
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...
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?
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.
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?
wonderful, so no need for a data gateway when using flow ?
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
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
Refresh via flow uses the 'API refresh'. It is limited to 8 times a day, just the way scheduled refresh works in 'Pro' license.
Millions thanks
You are very welcome.
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 ?
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?
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?
Hi Adam,
Can you please provide the shell script for data refresh.
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?
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
Thanks for great video,I have two tables one is history other one is live refresh. I want to insert rows in to history from live refresh on weekly basis. Both tables are identical I just want insert on weekly basis. I have tried append but it only append tables , for example history Table have Row 1 value A and refresh table brings Row 2 value B. Append will work fine first week it will show both A and B, second week live refesh will bring Value C . But Value B will be missing . I have tried Power Query Table.InsertRows I am constantly getting strange error messages. Please Advise.
1) How can sanpshot of data set works in power BI Data refresh? (In my case I need store weekly count somewhere that I can provide point in time reporting.)
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.
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.
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 user power automate with power bi report server?
where we can get rest API URL to trigger the refresh
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.
Hi Team, is there any way to refresh the data refresh based on any event - for example, when my DB tables have new rows OR when my DB data refresh is successful at the DB end. Any help would be greatly appreciated.
Please note that we are working on the paginated reports and have premium capacity.
Thanks,
Ram
Like Triggers
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.
Hi. Is it possible refresh a Power BI report in DirectQuery Mode? I have embedded a PowerApps form inside a PBI report, and when I clic "Send", all data goes to Microsoft Flow, and then to the database. But I don't see the new data on the report until I press the Refresh botton in the report. Is anyway to automathize this? I try with the "Refresh dataset" in Flow, but when I execute it, it fails: "Unable to process template language expressions in action 'Refresh_dataset' inputs at line '1' and column '2193': 'The template language expression 'json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_powerbi']['connectionId']' cannot be evaluated because property 'shared_powerbi' doesn't exist, available properties are 'shared_sql_1'. Please see aka.ms/logicexpressions for usage details.'." HELP!