How to EMBED a Power BI Report DATA Directly Into An Alert Driven EMAIL Using Power Automate

Learn how to set up a Power Automate flow that allows you to embed data from a Power BI report directly into an email. The email can then be triggered as a data alert when a specific value in the Power BI report is out with a given threshold.
In this example, I’ll be using Power Automate to send an email alert to a maintenance manager if the maintenance cost YTD is greater than the maintenance budget YTD.
The Power Automate flow will be designed to run every month. It will get the value for the cost vs. budget percentage difference. If it’s above 0 i.e. is over budget, then I’ll send an alert email.
The email will contain a table showing each month of the year and the budget cost, actual maintenance cost and budget vs. actual cost difference %. It will also contain a link to the original report in Power BI.
Note this approach uses:
- Power BI to provide the data
- Power Automate to process the logic that decides what action to carry out
- Power Automate to format and send out the email
You will also dip into:
- Power BI Queries
- JSON file structure
- Power Automate Expressions
- HTLM style formatting
The good news is that I’ll talk you through each of these technologies step-by-step.
----------------------------------
⏩ Video Navigation
----------------------------------
00:00 - Introduction
01:34 - Creating Power Automate Query
04:35 - Extract Trigger Value from Dataset
07:35 - Carry Out Comparison with Trigger Value
08:21 - Extract Dataset from Power BI
10:51 - Defining the Table Headings
15:10 - Formatting the Table Numbers
16:56 - Creating the HTML Table of Values
18:02 - Formatting the HTML Table
20:03 - Create Email With Embedded Data Table
21:08 - Format subject value using formatNumber expression
22:18 - Add embedded data table to email
-----------------------------------------------------
🎓 Power BI Dashboard Training
-----------------------------------------------------
👉FREE Power BI Quick Start Course - Learn how to create your first dashboard in around 90 minutes!
effectivedashboards.com/power...
👉 Power BI Quick Start PLUS - Learn how to craft KPI management dashboards that really do help you, your boss, and your peers make better decisions!
effectivedashboards.com/power...
-------------------------
📁 Resources
-------------------------
👉Access to the source PBIX files for my KZbin videos:
effectivedashboards.com/cours...
👉Article series on creating a maintenance management system data model and how to address the human factors of data collection for improved data quality. (This is not just relevant to maintenance and reliability data).
effectivedashboards.com/data-...
-------------------------------------------------------------------------------
📁 Resources and Sources Related to This Video
-------------------------------------------------------------------------------
👉Curbal KZbin channel exporting data to Power Automate playlist
• Export data from Power BI
👉Formatting Numbers and Dates in Power Automate
zeitgeistcode.com/power-autom....
👉Working with JSON files in Power Automate
tomriha.com/how-to-get-a-spec....
tomriha.com/how-to-select-spe....
👉Working with Arrays in Power Automate
zeitgeistcode.com/power-autom...
👉HTML Table Creator
codebeautify.org/html-table-g...

[@prasunabhimaraju9075]

thank you for the detailed explanation

[@brigittahegarini7162]

You are a life savior. Thank you for this video!

[@hamzakhalidabbasi2028]

The explanation provided was excellent and included a thorough, step-by-step walkthrough 👍👍

[@jasondavidsondata]

Hi Hamza, thanks for your comment and I'm happy you found the video useful 😀

[@user-hp8vy2co2j]

Great job Jason. Solid explanations without too much technical talk. Thank you

[@jasondavidsondata]

Thanks Andrew, glad you found it useful. 👍

[@waynewei3380]

Really like the idea of using performance analyzer to get query code.

[@NidhiSharma-gw2qk]

Thanks for the video! Very helpful!

[@jasondavidsondata]

That's great, glad you ground it helpful and thanks for the comment

[@anirbanaws143]

This has helped me tremendously. I am working on the next step of this. Say, automating it for many regions...and if for some region the threshold is crossed, it sends and email to the regional manager of that region only. Similarly for others....

[@pavanjaiswel8694]

Hi bro, I need some help with the automating data alerts for regions , could u revert back ?

[@samcrowson167]

This is a great video! Thank you

[@emrc9777]

Wow! Game changer! My mind is spinning thinking of all the applications of this… thanks!

[@jasondavidsondata]

Glad it was helpful! I was the same when I found out about this.

[@bhargavram9790]

Helpful video. Thanks a ton.

[@davepoisson2742]

Thanks for that one , very helpful!

[@jasondavidsondata]

Cheers Dave, thanks for the comment 😀

[@shubhabratadey]

I have been searching this video for such a long time...🤟

[@katiebroyles8432]

This was super useful and helpful. Is there a way though where I can format the grand total row to appear at the bottom of the other values leading to it?

[@charlesorly5536]

Awesome! thank you so much, this will be very useful

[@bodybuildingmotivation5438]

Make more videos on power automate, i love your content😊😊😊

[@healyd5]

Thanks Jason, great run through. How would you recommend formatting dates as they currently come through in ISO 8601 Formats. I tried using the formatDateTime function at the select state as below but no luck. formatDateTime(item()?['Source[Date]'], 'MMM ddd yyyy'). Any help is welcome

[@NicolasErrazuriz]

Thank you Jason, excellent video. Can I ask you for help please, Im not able to create the "total" row so that it doesnt show in blank.. thanks in advance

[@user-gw5dt3vl1h]

This is great and appreciate the stepping through the process. I am using Fiscal Week instead of Month and am having trouble sorting by the fiscal week number. Any tips to do that? Thanks

[@NicolasErrazuriz]

Hi! Im having trouble sorting also the tables, have you had any luck? tips to share? thanks

[@brunomanzi1857]

Thank you, it helped a lot! I will try to work on it. I have a question: I have a table that contains a lot of data, but only a few of them are relevant to send to my manager. So have to take out all "null" values by filtering in that filter area in "filters on this page". When I copy the query for the table, it also applies this filter? I am afraid to send a huge table with irrelevant values. Thanks! Awesome video :)

[@jasondavidsondata]

Hi Bruno, thanks for the comment 👍😀 When you copy the dax code for the table visual it should include the necessary code to filter the null values.

[@yasiridris1078]

This is great! What if I have a column with email addresses and I want to send the email to the associated emails with the applied dax filter?

[@roex005]

THIS is exactly what I am looking to do as well

[@luisfernandomacedo1451]

Great job, sir! If I wanted to send more than one visual, must I need to add an AND() condition to the logic? I was thinking about sending the columns chart and the table

[@VishalKumar-np9bi]

Thank You! sir, This video helped me a lot to accomplished what I wanted to do in Power BI and Power Automate. Please make a video on formatting the table cell with different colors based on the values which each cell satisfied.

[@jasondavidsondata]

Hi Vishal, thanks for your comments 😊. That's a good idea for a video too.

[@belenlopezalduncin9278]

@@jasondavidsondata Hi, have you done a video on this in the end? I've been looking everywhere. thanks for the video it was very helpful

[@jasondavidsondata]

@belenlopezalduncin9278 not yet, I'd completely forgot about it. I'll add it to my list though but I'm not sure how long it will take I'm afraid to say.

[@EPDBerkshire]

Hi All, This was a very helpful... How can I display the total row at the bottom instead of the top?

[@zdeneknovak4350]

Very nice!

[@astralyog]

Fantastic!

[@jasondavidsondata]

Cheers, glad you found it useful. 👍 😀

[@nurazreenaziman9606]

Thanks for the video! but can i understand if this flow will automatically get the updated data from the scheduled refresh that we set in power bi service, and the data will be updated in the table embed?

[@jasondavidsondata]

Hi, The flow will use whatever data is in the power bi report when it runs. So if your power bi report has a scheduled refresh each morning and you run the flow after the scheduled refresh it will create a table with that data embedded in it. Hope that answers your question. Cheers Jason

[@nurazreenaziman9606]

@@jasondavidsondatareally appreciate the answer Jason, this video is so helpful!

[@Adam_Pagan]

Thanks for the great video. Would this method apply to embedding a Matrix as well?

[@user-kj7uf3xs5j]

Hi !!!! Tihis is an excellent tutorial !!! Seriously, saved me a lot of time. I have a question though... The grand total goes at the first row (just under the column headers) of your HTML table example... Is there a way to move it at the bottom ? I know the HTML code to format the last row... but right now my grand total aren't the last one. Thx in advance.

[@user-kj7uf3xs5j]

I found it !!!!! Just need to change the DESC to ASC into the Dax Code at section ORDER BY at the bottom : [IsGrandTotalRowTotal ASC,

[@andreicostin3]

Hello, nice video as usual , do you know guys how to get rid of the blank values working with the same method were should i add another step ? Thank you! :D

[@user-zx7oe5uy8w]

@Jason, a quick question, i have a dashboard with several filters, how can i apply different filters and send mails like this video..is it feasible?

[@cheelokwong4114]

hi Jason, can I ask have you encountered this error before? The variable 'Number of Draft per month' of type 'Array' cannot be initialized or updated with value of type 'Integer'. The variable 'Number of Draft per month' only supports values of types 'Array'.

[@cristobal8336]

Nice, but how can I filter by a value when a value of table will be X for example?. I have a lot of rows, but I need to know how can I send a email when a value of row in that table equals X value.

[@danielcarvalho2158]

Hey @Jason , I was wondering if it is possible to do something similar to this but instead of it being if a specific value of KPI changes it sends to the people we choose, I wanted to drill it down more. So if that value was changed but only because of a specific country then only people from that country would get that email. For example, I have a target of 5 apples per day and only one of the countries goes below that target, then i want only a specific person of that country to get an alert email...

[@jasondavidsondata]

Hi Daniel, sounds like it should be doable. I'll look at creating a video if I figure it out.

[@VanessaSuan]

@@jasondavidsondata did you create a video like this? I need exactly the same. Thank you!

[@yeloSolo]

Mate I hope this video is not taken down by tomorrow!

[@shaikabbas6048]

When i update the data in report next day will the power automate flow will email that updated data or this static data only everytime we run the flow

[@yeloSolo]

It refreshes with dataset, the DAX will be applied over the dataset.

[@prasunabhimaraju9075]

Also in select we are getting Column Headers, how to get column values?

[@jasondavidsondata]

Hi Prasuna, To get a specific value you can use the approach explained here: kzbin.info/www/bejne/qpOxp4GhZpamjq8 The same principle applies to getting specific value from a table. In this video use action to extract the whole dataset so get every table value as in kzbin.info/www/bejne/qpOxp4GhZpamjq8 this part of the video. Hope that helps, Cheers, Jason

[@cristobal7072]

How can I applicate a condition in each value of a table if I extract that in a query of power bi?. You used a card, but I need filter a table . Help me please

[@vidya7453]

its soo good .my work is done but its not show percentage value .for my side its show as number .what to do

[@jasondavidsondata]

It might need you to wrap a format statement around the number in the power be query. I think I cover it in the part of the video.

[@bethfollenweider1472]

Great video but it's mind boggling how easy this was to do in SSRS and how painful this is in PBI. How is there no tool to solve for this yet?

[@user-ld8wy9lw8c]

When I run my flow, I could see only header in the email. and values are all null. Could you suggest what could be wrong here. Thanks,

[@niranjanmakkuva4639]

Is there a way to add the report as xlsx attachment instead of link

[@nivethak5116]

how did you get ('Get_YTD_Cost_vs_Budget_Pct_Value')?['body/firstTableRows'][0]?['Total_Cost_YTD_vs_Budget_YTD_Diff_]'] Could you please explain how you got this expression?

[@hyabhi]

Seriously need help to understand this output stuff at 5:54 that is being used as target value. Any help is much appreciated.

[@7MoOdii86]

How to do This in power bi desktop?

[@bharathharishkumararumugam1621]

Like that i want to sent multiple tables in mail in a single report

[@RohitThakur-ku8sb]

Plz help , i can't get values other than month in select step, they all show as null , followed exactly same as shown but can't get it right. plz plz help

[@jasondavidsondata]

Hi, without seeing more of what you are doing I can't really help. You can ping me a pm on LinkedIn if you want to exchange emails. www.linkedin.com/in/jasondavidson

[@RohitThakur-ku8sb]

@@jasondavidsondata Hey , I tried and I was able to get it finally, I really appreciate your quick response. I will surely connect on LinkedIn. Thanks again 🙏

[@saptarshidas4456]

This is awesome

[@tendulkarp1561]

How to i Contact you sir

[@PrabhatKumar-tn3be]

Hi, It looks like that measures do not export, only text content can be exported to csv. Is it possible to export the table with text and measures both, PLEASE? I see the values in "Input" but "Output" shows "null" therefore nothing saved in csv file. // DAX Query DEFINE VAR __DS0FilterTable = FILTER( KEEPFILTERS(VALUES('_____PrerequisitesPK'[Deployment Date])), NOT('_____PrerequisitesPK'[Deployment Date] IN {BLANK()}) ) VAR __DS0Core = SUMMARIZECOLUMNS( ROLLUPADDISSUBTOTAL( ROLLUPGROUP('_____PrerequisitesPK'[Installation Time GMT], '_____PrerequisitesPK'[Batches]), "IsGrandTotalRowTotal" ), __DS0FilterTable, "PK_Dist_Count_Exclude_Offline__14Days", 'Measures PK'[PK Dist.Count Exclude Offline >14Days], "PK_Dist_Count_Offline_Exclude_Offline__14Days", 'Measures PK'[PK Dist.Count Offline Exclude Offline >14Days], "PK_FT_Pending_NOT_on_WiFi_Exclude__4Days_Offline", 'Measures PK'[PK FT Pending NOT on WiFi Exclude >4Days Offline], "PK_FT_Done_Exlcude_Offline__14Days", 'Measures PK'[PK FT Done Exlcude Offline >14Days], "PK___FT_Done_Exclude_Offline__14Days", 'Measures PK'[PK % FT Done Exclude Offline >14Days], "PK_INS_Done_Exlcude_Offline__14Days", 'Measures PK'[PK INS Done Exlcude Offline >14Days], "PK___INS_Done_Exclude_Offline__14Days", 'Measures PK'[PK % INS Done Exclude Offline >14Days], "PK___INS_Pending_Exclude_Offline__14Days", 'Measures PK'[PK % INS Pending Exclude Offline >14Days] ) VAR __DS0PrimaryWindowed = TOPN( 502, __DS0Core, [IsGrandTotalRowTotal], 0, '_____PrerequisitesPK'[Batches], 1, '_____PrerequisitesPK'[Installation Time GMT], 1 ) EVALUATE __DS0PrimaryWindowed ORDER BY [IsGrandTotalRowTotal] DESC, '_____PrerequisitesPK'[Batches], '_____PrerequisitesPK'[Installation Time GMT]

[@RohitThakur-ku8sb]

You found the solution, plz share as I am also looking for the same