Power BI and Microsoft Flow - Monitoring your data
Вставка
- Опубліковано 2 жов 2024
- In this video, Patrick shows how you can use Microsoft Flow, combined with Power BI, to monitor data and alert you when something is available.
This takes advantage of a streaming dataset and using the schedule recurrence in Microsoft Flow to trigger an action as Patrick wasn't using anything else to trigger it. You have a ton of options though.
LET'S CONNECT!
Guy in a Cube
-- guyinacube.com
-- / guyinacube
-- / guyinacube
-- Snapchat - guyinacube
-- / guyinacube
**Gear**
Check out my Tools page - guyinacube.com... - Наука та технологія
Men, you deserve the Peace Nobel Price for your contributions. You have really helped me in my job, really appreciate
Thank you so much for your explanations!!! Here in Brazil, we´re on Power Bi and Power Automate!!! Going to the next Level my friend!!!
This is great. Literally implementing this right now after watching.
Yet another great video. Keep up the great work.
Thanks Edwin!
Yes! Thanks so much for making this video Patrick! Stoked to connect on more scenarios in the future!
Thanks, Jon!
This is exactly what I’ve been looking for! Is this type of connection able to be run on desktop as well? It looked like you built out your report online rather than desktop.
Great video, thank you Patrick! Very disappointing in my case does not work because ms flow see my workspaces but do not see my datasets.
That's interesting. Do you know why it doesn't see your datasets? Is it an actual streaming dataset?
Legend. Great, snappy breakdown. Cheers.
Patrick is the most gangsta Power BI Nerd I know.
I don't get it... how is he gansta? He seems like a normal person...
@@karabarr8712 When he says "Their certificates are a little more granular" .... that's gangsta. ;)
.... OR K0uikku thinks all black people are gangsta :/ ... we're not.
Gangsta just means so good at what you do, it’s scary. Your excellence and level of success is a threat to somebody lacking your gangsta. Lol
You sound like a racist.
Patrick thank you so much - learn a lot from your video. keep the good work.
Awesome! Thanks for watching Patrick!
nice video but too fast
Can you use flow to determine is a refresh was successful and if so then send the subscriptions or stop a subscription from sending if criteria is not met?
I can't give you 2 thumbs up 😁 ( that's will undo ) but what about odd number 3 or 5 lol
you guys are rocks !!!
What a great video, Patrick. Anyway, can I create a dashboard using data source from GitHub?
Hm good info
Hey. Had a quick question.
If we use a branching form and a field is left blank, the FLOW fails. What is to be done in such a scenario ?
Yes, Power BI Service and Microsoft Flow are powerful combination.
Thank you, nice video.
Thanks for watching!
Is there a way to embed a dashboard or report within an alert? I have an end user that wants the alert, but also wants to see the visual associated the alert. Make sense?
Hi, thank you for this video. I'm still learning Power BI, but how would you create the Power BI report you showed with the gauges? How did you create the 'Days Left' table below each gauge? Thank you!
Thanks for the video.
Hi Patrick, great video as always! I would like to send an Adaptive Card to Teams Channel with data from PowerBi. My data is in a SQL server table. Any clue?
Hello Patrick excellent video please help with a problem that I have is when the event is triggered I add what is based on what is in the graph producing duplicity in graphics. There is some way that does not add just add me the new. Thanks
Hi Patrick
Great video! Thanks.
I've being thinking to use this method to make email notices to my clients for there open debts.
Can power bi send automatic emails, to every client, which have open debt from a table in the report?
I mean that there will be some tamplate which will be send to each client:
"Dear xxxx
You have an open debt of $ 1,000 which you shoud clear within XYZ Days."
By the way,
Can i add to my report a button which will run this operation of sending those emails?
Dear Patrick, do you have an example of how to create this gauge visual. I have the month's left column ready but I'm trying and it doesn't let me filter as "expiring < 30 days".
Hi Patrick ,
I have a Stored Procedure which expects parameters.
Step 1 - I need to execute this stored procedure in Power Apps.
Step 2 - Insert the result set of rows to Power BI Dataset in Power BI Service.
* I am expecting only the queried data in Dataset
* Before i make another query using Stored Procedure in Power Apps the previously loaded data in dataset should get deleted.
Can be this Done.
why are you not talking about refresh time limit, there is limit so you cannot have more then 8x refreshs in one day!!
Hey man, that's cool but can you create another video with different type of data set? i think it will be the same procedure! but you know, just in case ! thanks !! cool videos! Hi from Costa Rica~ pura vida
Hello great video, i really appreciate your content, my question is isn't this process only valid for live dashboards? i think if you have this process on a traditional power BI report with custom visuals, it won't work. do you have any suggestions for an alternative? than you
Hello, just one question please sir. I used to trigger a value in a table and based on that an Email should be Sent to a responsible person. Now i want to automate the process to higher Level so the responsible that gets that Mail should Provide me With Data. Is it possible to let this responsible Person reply on this Mail With the required Data und this Data should be directly inserted in a specific filed in the Table (i am actually just using a flow With condition control) . Looking forward for your answer and thanks alot in advance
Any idea where I can get a cool guide like this but using Excel Spreadsheet/Sharepoint instead of SQL????
Hey Patrick. Where Yooooo! We didn't get to hear that in this video. Good job man.
Great video! Can the last step (the alert) be done within flow? Instead of in Power BI?
Yup. Absolutely! There are a lot of different ways to do it. In this scenario, they wanted a Power BI Dashboard to go along with it, so we did the alerting from the Power BI side of things, but you could totally do the alerts from with Flow directly and do it in different ways.
Can I use Flow to trigger a Push Notification on my Mobile Device if Flow is downloaded to that device?
You absolutely could!
Thanks Jon! :)
Is it possible to send an email weekly using flow that takes one part of a power bi report (list of emails). The key is not using a data alert but just grabbing something in the report once a week. I haven’t found anything yet
Thanks for showing a practical use case for integrating PowerBI and Flow. Fantastic job Patrick!
The video is very helpful.. Can we create a slicer for different streaming datasets in dashboard
Interesting topic. Enjoyed it. Thanks.
Great video, Patrick! first time I see Ms Flow in action. Will give it a go. Question: Is it possible to achieve the same result (real-time alerts) by using Direct Query in Power BI instead of Ms flow?
It is based on the dashboard tile. That tile could be backed by different data sources. Just be aware, that you may incur multiple queries to your DirectQuery data source and may end up hitting some limits. There are limits on how many queries can be sent per second. Just depends on load to that data source.
Hi Patrick, could you please tell me more about the limit of DirectQuery (how many queries we can use at the same time?).
My company have a lot of report that based on this mode and by consequent we have a lot of users that use these reports at the same time...
Hi Patrick, got a little issue which somehow relates to this. Can I access information from a datasource in Flow? I have to send a notification email with a list of projects for which a KPI got over a threshold of 30%. The measure is calculated in PowerBI and it is based on fields from multiple datasources(MySQL and Azure). I would normally do that by executing a query and filtering the relevant information inside the query, but I cannot do that because I need to merge that from MySQL and Azure in the query, which for now is not possible (already did that inside PowerBI). Any ideas?
How can I get the web link to act like a PDF (people can't change slicers)?
Hey Patrick, your videos are killers!
Can this work with Excel or do we need SQL server or something similar only?
Good video guys ! Nice idea, i'm a n00b in Power Bi, i'm management controler and looking for news tips in reporting, and seems to be realy good to work with this product !
So many possibilities :) Thanks for watching!
Hi Patrick,
I have 2 questions:
1/ Could you please tell me more about the limit of DirectQuery (how many queries we can use at the same time?).
My company have a lot of report that based on this mode and by consequent we have a lot of users that use these reports at the same time.
2/What happens if we dont insert new data in SQL Server? Will the data alert of Power BI still function (because we have the recurrence every minute We don't have any new data in Power BI but I dont know if the Power BI dataset will refreshed or not (to activate the data alert - I want to do some kind of check : If I dont receive any new data in 24h, it sends me an alert)
Thanks,
What is the visual did you use to show details under the gauge? Is it a table? If yes, how did you format it?
Was a while ago. Believe it was just a table.
Do you do scorecards for businesses to track KPI’s? If so how much?
HELL YEA!!!!!!!!!!!!! Going to talking about some Hustle & Flow...ops wrong channel.
Hi Patrick, i am watching your videos my problem is to expand from 200k of rows to 5m of rows on real time??? I see that it´s possible but i don´t know how to do it. Thanks for all
200,000 max rows stored per table in FIFO dataset
5,000,000 max rows stored per table in ‘none retention policy’ dataset
How do i send the alert to a respective person handle the certification?
Hi Patrick,
I am Following the Same Process you Mentioned, After every 1 minute of Refresh my Data is Being picked again and again.
Here is my problem statement:
i have a Card in powerBI which gives count of rows in table, I have 40 rows in my table.
after 1st minute of microsoft flow execution my card showing value '40'. But from second flow it is being Multiplied again and again.
Is there any way we can refresh/add the new rows from sql database instead of appending the whole data again and again.
Please let me know if you have any solution. i Believe you might have faced same problem with above scenario. :)
Hey! great demo on this one! Just a question, is it possible to integrate this with MS P.BI ? like, MS Flow would refresh my MS P.BI report then email its results via MS Flow again ? considering that these recipients haven't subscribed on my MS P.BI report, yet. Thanks in advance :)
******* Hey, can any one give solution for this " i need to insert data from one sql server table data to another sql server table ", but we need to create it by using a query only in Microsoft flows ****************** please its very urgent
Hi, is there by any chance a mean to get the error message when there is one ?
Glad you mentioned SSIS, started using flow recently and thought exactly the same thing! What's the license implication of setting up a project like this? If we publish the report to 10 users, we need 10 powerbi licenses. Do we also need 10 power automate licenses?
Awesome demo.
Good stuff as usual
Hi Patrick, I am trying to automate my Power Bi dashboards using this template: Update Power BI dataset when a SharePoint file is updated. But I did not get it because the flow did not recognize the dataset and the table that already exists in the Power BI.
Could you please make a video or give me some tips to get it! Thanks in advance
A SharePoint file? Can you explain a little more? When you say SharePoint File, you mean one a new file is uploaded or overwritten?
Loved the video. Is there a FLOW dashboard to see and monitor the current status of the various/many FLOW(s) we have running. Get notifications, if one failed, etc.
Great video!! Well done! 👍👍👍👍
Hey Patrick I need to refresh my dataset for every 5 min....All I need is to set a custom refresh to the dateset.Please guide me in the process.... Thanks in advance
From a scheduled refresh perspective, you can only do this every 30 minutes from the UI. You could alternatively call the refresh API to trigger a refresh at any time (but today there is an enforced limit on how often you can call it).
Super super super good man,,,, I've been looking for more than 3 days for the best example on this thing..... Brief and to the point,, can't explain how satisfied I am... Gonna sleep well.... KUDOS...
Also,,, since it's directly related to API,, could you please put API somewhere in the title so the people looking for it could find it easily without wasting their time and wandering into many videos.
Great video! I noticed that when the data comes into PBI from the flow, there is no way to edit the data set such as in transform data, or even an option to format. I have zip codes coming through the flow, and since they aren't coming through geo-coded, I cant get them to populate on the map. Any ideas?
Fantastic video! I use alerts for monitoring physical cash holdings in our retail store's safe, which exceed our insurance limit for individual stores. I get the emails which get redirected to the area manager(s) for their store(s) which exceed the limit. It's a small part of a full cash flow tracking system I've built in power bi.
@patrick can we use flow and power BI for managing outlook emails? I see so many inbox of users having thousands of unread emails. I thought of using flow to categorise them and monitor using Power BI. Can you please help me on this? Is it actually possible on the first hand?
Patrick, I've got a really dummy question..what did the MS Flow did in this scenario...can't we setup an alert as normal from Power Bi after connecting to sql? please don't laugh at me :)
I got the same "dummy" question! Why not just use Power BI for this case?
We used flow to populate the streaming dataset. This was just an example of how you could do things. There are many different ways to accomplish the same thing. You could have done all of this (including the alerting) within Flow if you wanted.
We used SQL as the data source that Flow connected to, but it doesn't have to be SQL. Maybe it is an IoT device. Maybe we are reading directly from some REST API out there. This was just to illustrate what can be done, and give folks ideas.
Is there a way to use flow to open up .csv files that are emailed to me and add them directly to PowerBI?
What's the advantage of using flow vs simply direct query?
Hi Patrick , actually is there any way wherein I can set up a flow for issues such as the gateway is down( we get message in power bi web that data source is not accessible), and i get notification in mail
Is there a way for MS Flow to automatically trigger an excel report refreshed? Acts similar to an IS package that runs just to refresh an excel repot?
Really super. Great one (y)
Thanks for watching Hansa!
Hey instead of using flow to make your data refresh, we can use direct-query and then apply alerts.
You could. There is usually more than one way to do something. 🙂👊
This is awesome. I want to do something similar but using SharePoint as my data source. This has given me a great start. Thank you! SUBSCRIBE!
You should be able to do it with a SharePoint list
Great! Thank you!
Actually I do the same flow but now with the flow - I use Direct Query & Power BI Service to monitor my resources.
But it seems that with the flow we have a very closed real time!
Thanks!
Also, by using flow you can take additional actions based on the data. For instance instead of just notifications, you could create tickets automatically or have emails sent off to various teams that need to renew the certs.
We can do it also by set an alert in Power BI Dashboard no?
thanks Patrick, great easy, informative, awesome video. I like the use case, pace, and live working demo.
Another great video. I plan to use this for sales in my organization to show when we have not gotten business from a particular customer if they hit threshold of X amount of days, if sales is X amount % less than the previous month and this would just trigger sales to make the call. I really appreciate these videos. Cheers.
How to create calculate column using four multiful columns see below
I have 4 columns like ASNp id,CRM id,,,MCS id,AOM ID each column contain a data below
ASNp id=ASnp,N
CRm id=crm,N
MCS id=MCS,N
AOM ID=AOM,,,N
Based on using above columns we will create one column LIKE AOB
When I using AOB in sllicer it need diplaying like below
AOB
ASNPID
CRM ID
MCSid,AOMID
I like your videos. Just that i find them a bit too lengthy..... :( good topics though.
Thanks for the feedback! And, thanks for watching.
Any special reason for using alerts in PowerBI and not in MS Flow?
Just one approach. You could go the other way, and just use Flow to do the alerts, or to trigger other actions as well. Thanks for mentioning that!
one additional in my case, my customer just wants to see new changes of their customer balance in through SMS instead of notification of psi app, so i need to use PBI + MS Flow + Twilio
Hi Patrick
Really nice video. I just copied your setup and you are right - it just works. However in my scenario im interested in truncating the dataset by the end of each day. How can i empty that dataset in Powerbi.com? I searched for it - some said that enabling historic data back and forth will do the trick but thats not quite the automated solution i was hoping for. Any advise?
Thank you on behalf.
u can use ssis to replace the files in that folder with new ones. Let me know if you need help setting up ssis.
Thanks patrick for this video. I will do this for real life scenario where in we can give frequent updates to our customers. 👍
Man, you are great! Thanks for sharing this valueable stuff with us!
You're welcome, Andre! Thanks for watching!
Why do you use Flow to refresh data in Power BI?
Could have used Power BI Gateway instead??
is it possible to set a sort of RSL - Userprincipalname on this Streaming dataset?
Unfortunately, no.
My flow adds records to Power Bi multiple times :( Why?
What triggers the flow?
I am using the Excel file stored on Onedrive for business and having the same issue, i guess as we are triggering the flow every minute, it is pushing the data as historical is also on
I have the same situation with multiple records. I set Flow as Recurrence every minute, Get rows from SQL and add rows to dataset. Is it possible to add only new records or clean streaming data source before load new data?
Is this implementation valid with RLS, so everyone get alert according their gauge?
Hello!
Great video this helped alot!
The only problem left is when we edit the report as shown at 8:15 then the stream doesn't work anymore it just captures a static image of the report when we pin it to the dashboard.
This seems to be a bug or someting, check the video below also. It shows the same issue.
ua-cam.com/video/0YIBHfgasok/v-deo.html check (39:00)
Do you have any ideas?
//A
Correct. In order to actually have a streaming tile, you need to create a streaming tile directly from the dashboard. Anything in the report itself will not be streaming but based on the historical part of the streaming dataset.
@@GuyInACube Thank you for answer! Okey I understand, is there a solution for this maybe with a PowerShell-script or something? Or its simply not possible?
Hi will Smith.
Keep going!!!! Super cool stuff. Would love to work with you one day
To be more serious, Patrick, could I use Flow, Power BI and "historical data analysis" to have a current and historical analysis of my current dataset (contracts in MS CRM Dynamics, status "active") which is now analyzed by taking a snapshot from the SQL database every week? I mean, because we monitor our active contracts level (the bigger, the better, more sales) this could have a more real time analysis of the active rows in the datasets. Now, yesterday, three days ago etc.
Thanks again for a great video!
Yep. There are many options to accomplish this. You could follow the same steps in the video, however if you connecting to a SQL Server that is On-Prem you would need to use the on-premise data gateway.
default values to displayed can you please help for the same
Any solution for FTP to Power BI Gateway connection?
Great video Patrick! I am setting up something like this to monitor my SSIS Packages and send users notifications about errors and warnings during the processing.
That's awesome! Yeah, it has so many different uses.
Hey, could you tell me how you connect to SSIS using microsoft flow? Thanks
Paul, I have a table in my data warehouse where I store SSIS logs, so I get this table conected to Microsoft Flow and set up the flow to notify the users.
Man you're a lifesaver. Thanks!
Glad to help!
for some reason I keep getting error 500. while accessing SharePoint onpremise list. I don't get it on tableau though. can you please help. are there different connector in tableau and pbi ?
Yes. For on-premises data, you need to use the On-Premises Data Gateway. You can learn more about the connectors here - docs.microsoft.com/en-us/connectors/. You may want to look at the gateway logs to see if that has additional data.
Is this dashboard updating LIVE?
Hey Patrick. Thanks for the nice video. So, flow, in this case, was used to refresh the dataset every minute to get an alert up & running ? That's it ? We can use direct query now or import data with as much as 8 refreshes per day with at least 30 minute intervals. Why isn't this just an easy thing to do in power bi without the help of ms flow ?
As mentioned in another comment with the same feedback, this was just a way to illustrate the possibilities. Flow didn't refresh the dataset, it inserted a row into a streaming dataset.
We used SQL as the data source that Flow connected to, but it doesn't have to be SQL. Maybe it is an IoT device. Maybe we are reading directly from some REST API out there. This was just to illustrate what can be done, and give folks ideas.
Guy in a Cube aha! thanks for clarifying : )
Hey, great video! But when I use Power BI Desktop to conect the dataset I can't "transform" the table in "Power Query". Am I doing something wrong?
That is because it uses the Live Connection to connect to the dataset. You don't get shaping and modeling functionality when doing that.
Amazing the possibilities it offers !
how can I get the email sent out to different people in my organization for whom the certifications are expiring? and maybe cc myself and their managers
Currently alerting/subscriptions don't allow you to define an email address to send it to. You could potentially use Flow to monitor your inbox and then send the alert to other folks though.
A VERY ENJOYED EXPRESSION THANKS
But I thought one didn't need Flow to integrate AzSQLdb with PowerBI.
You don't. You have a ton of options. This was just an example of a way to use Microsoft Flow with Power BI.
I'm new with MS Flow so could we use on-premise connection for SQL Server in MS Flow?
Yes! By way of the On-Premises Data Gateway. You can use the On-Premises Data Gateway with several services - Power BI, PowerApps, Microsoft Flow and others. Install one gateway, and use it with all of those services.