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

hi this is jason from effective dashboards helping maintenance and reliability professionals get the most out of power bi so welcome back to another video in this video we're going to be talking about automated email alerts so we're going to be sending an email whenever a threshold so for example this value here is above a certain value now we've covered this before in previous videos however the difference this time is that we're going to send an email that's going to contain an embedded set of results so this table here is going to be embedded within an email and we're going to have some custom text and we're also going to embed a link here that's going to be able to directly take whoever reads this email back to the underlying power bi report so we're going to use parallel automate and we're going to use a new feature that i've never covered before for power automate to um to create this and i'm going to talk you through each and every step okay so this is a report we're going to use and this is a value here so this is telling us that we're 2.76 percent above the budget for this point in the year so for august up to august and we can see here it's represented as a accumulative cost and accumulated budget here with 2.6 ahead of the budgets we've overspent and if this value is above zero then we're going to send out an email that contains this information here in this table okay which is pretty much the same as this i would probably never put these two in the same dashboard but just for for the sake of demonstration purposes it's in here just now so let's go into power automate and we'll start going through the steps to create this automated email so it's monthly cost so i'm going to or the course updated monthly so i'm going to create a scheduled cloudflow and we're going to say okay and we're going to start this as of next month and we can write at 10 am and we're going to repeat every every month okay and then we'll just create that so we can see here we've got a reoccurring cloud flow here interval every month the next one we're going to do is we're going to go and use uh an action that's going to run a query against a power bi data set okay so run a query against a data set and in here we're going to enter the workspace on the data set and the data set here is going to be maintenance course dashboard and then we've got this query text here okay so if you're proficient at writing queries against a data set then great you can just enter that information straight into there if however you're not like me then what we can do is go back to the power bi report or the pbix file so the report and probably a desktop and we're going to go and view performance analyzer we're going to use performance analyzer so we're going to go and start to record and then refresh visuals so each of the visuals has now got a section here and this is the visual that we're after okay this one here so we just need to find that description in here which is this one here here to date cost versus budget and then we can see there's an option at the bottom here to copy the query that created this number here so we're going to copy that and then we're going to back into the the parallel automate action and we're going to go and just paste that query into there and that's all we need to do okay so that's going to give us all the information we need to actually generate that number so i'm going to go and test this just to make sure that the number has been generated correctly so to do that i'm just going to save it and then i'm going to click on test and i'm going to click on test manually and they're going to run float so i do like to do a test every time we add a significant step just to make sure everything's running properly okay so the flow's run successfully and what we can see here and this is great this really helps you debug what's happening workspace data set and here's the query that we've run and here's the output here okay so we've got basically an array that contains an object so that's what these so these square brackets indicate that this is an array and then within that way we've got one object which is basically got this um combination of properties and values so the property there is total cost year to date versus budget year to date difference and here's the value here okay so it's exactly what we need so the next thing we need to do is we need to go and extract this value here so that's going to be the the next step we're going to do okay so i'm back in power bi and i'm going to go and i'm going to rename this to be something a bit more meaningful so get ytd cost versus budget percentage value okay so when we go back and look at this we understand exactly what's happening here we're getting that value of this query so i've got to take that value and we're going to store that in available so let's type in variable here and we're going to initialize a variable okay so that's basically getting available defining it and we can also set the value the initial value of that variable so we'll call this target value and we will create as a float and in here we are going to type in an expression that's going to get that value there okay that's 0.0276 i think it was okay so to help understand how this expression that we're going to enter in this value here is constructed of um i've just opened up a notepad here so this is the the output from this step here and we can see it's a fairly straightforward output it's an array with one object which is basically a table which has got a value and a property and a value okay now the expression that we're going to type into this value here is going to be outputs and it's an output of this here which is going to basically be refer to this section of code here and we want to use only the first table rule now we'll cover that first table will function a bit later on but basically there's only one table here in one row so it's fairly straightforward here and then we want to then go and extract from this the first row which is basically referring to zero here okay and then in this row we want to extract the value of this property here or the tribute i'm never sure what property attribute but basically this name here which is this here okay so it's telling us to go to the first row in this table and get the value that's associated with this property here which is this value here okay so that's pretty much how that works so let's copy that and we're going to go back to value we're going to go to expression and we're going to enter that into there and click ok and just hover above here and just make sure it's been pulled in okay that looks good and we are going to go and just save that okay now i have created a set of comprehensive notes that come along with this it explains that in a bit more detail so you'll be able to get down those download rules those from the resources section so let's contest this so we've now everything's run successfully so in here we can see that we've now got this number isolated here okay so now it's isolated as a number we can carry out a comparison against or some logic check against that particular number okay so the next step now is to add a condition so let's go into condition and we're going to add a condition control here and basically what we're going to do is we're going to check to see if this variable here this target value that we identified here so that target value there is greater than or equal to or greater than zero okay so it's greater than zero it means that we're above the budget now if it is then it's going to go down this like here if it's not it's going to go down this like here so if it's no then we're going to be we're not going to do anything now you might want to send an email in here but for the purpose of this we're not going to do anything if it's yes we're going to go and send that email so the first thing we need to do is add an action that's going to go and pull the data from the the power bi report so let's go back into our power bi report here and we've still got performance analyzer open and this time we want to go and we'll refresh the visuals again and this time we want to go and get this cost versus budget with difference so is it here okay well either one of these we do actually but we'll go with this one here and again we're going to copy the query right and then we're going to go back into our power ultimate okay so we're back in here and we're going to go and use the same step here first action here which is to run a query against power bi there we go one query again is probably against data set and it's under the power bi section and again we're just going to put in the information work management and maintenance cost control and then here we're going to go and paste that query in now this p this query has got a lot more to it as you can see here okay so but it's going to pull them back that table of results into this particular data set here okay it's running that data set and pulling that data set back into here so that's has now got that data set okay so we're gonna go and run this just to test it just to make sure it's working okay now we can see it was above it was true the expression was true for this condition and it went down this like here we can see it's run this this action here and in here we can see that we've got this first table rose output here okay and that's what exactly what we need and it has got can you make a make sure you can make it bigger but we can see here it's got an array of objects so the first each one of them is our row in the table now this one here's a grand total is true and that refers to actually this will here this one here it refers to and then we can see let's go next one so take january 2022 and we can see that we've got the calendar month um that's a number for the calendar month it's not the grand total and we can see the maintenance cost is six one six one one now we can see here okay and it's going to start to basically and there's an object in that array for each one of these rows in the table which is great and is exactly what we need okay now the thing that we've got here if we look here is that the table headings for each of these is going to be this value here okay so these are going to be table headings so they're not particularly great for um particularly this one here for entering into a table that you want to embed into an email so the next step is going to be to change these okay so i'm back in power automate and we're going to add another action and in this action here we're going to use another day operation called select okay and it allows you to select from this um this query here or any query basically but we're going to select from this one i'm just going to change this here to be [Music] a different name okay just allow us to make sure that we know what that actually is and then we're going to select from now if we scroll down here we can see that we have now got the option to select from the first one that we had which is get the the monthly value or this is get the year to get cost versus percentage value so it's this one here and its first table of those which is going to be the output from here and then we can see we've got the option to map a key and a value so the key is going to be the new name so let's call this month and in here let's call this an expression or we need to use an expression basically so we need to go this expression here and in here we're going to start off by writing the expression so it's going to be item and then open brackets open close brackets and then we need to put a question mark and then open square brackets and then open these commas here so the item that you're going to extract from this result here is going to be in here so the item will want to open this up the first one is going to be the date so let's find the date in here and here it is here okay date calendar month year so let's copy that i think actually i should do that first yet okay let's do this again expression it's lost it item question mark and then in here i'm going to paste this okay so that is what we need here now one of the things i've noticed is that it's actually surrounded this here with um with these open and closed because this is a date because it's a table name now you don't actually need that there okay so that is what we need okay so month item so let's just test this first of all just to make sure it's working and then we'll go back and add in the other ones okay so we're back in here i've run the test we can see it's all been successful so let's open this up and we can see these are the inputs and we can see that the month well for the first row it was null because that was a grand total of all but we can see now that this now has got a month okay so that's going to be the column header and that's going to be the value associated with that month so i've just done it for one at the moment just to test it but we're going to go back and do it for them all okay so that's me entered in the items for each one of these i've changed that to cost budget difference percentage difference now one of the things to bear in mind here is that just getting the syntax right here just make sure you get the syntax right here because it's not always 100 obvious but if you go in here we've got item now this here needs to be in square brackets with the with the the single commas each side the single quotes each side and then square brackets again okay so just make sure that you've got that right so let's go and test this and make sure that it returns what we think it's going to return okay so we'll expand out the select statement and in here we can see now that the the output of the further select statement rather than have these rows here as the headers we've got these here okay month cost budget diff and diff percentage however what we have got is these numbers here these formats here so these formats are not particularly pretty so i'm going to show you how you can actually address that okay so you could format the expression here and i'll explain how you format an expression later on but for this one here i'm going to show you how you can format it in the actual query itself so we open up the query and what i've done here is we can see that this one here this budget for example is just a maintenance budget here the maintenance cost i've actually wrapped this with this format statement here okay so format and then open comma open brackets and then of um basically the format function takes a value which is this maintenance costs um and then a comma and then in inverted commas i've put this format here which is a form that we're going to use to actually convert it into actual actually something that looks like pounds and um i'll leave a link below to the the format options that you can use but that's going to be perfectly fine for these examples here um maybe different in your country for dollars for example so i'm just going to go and wrap a format statement around this budget here the total cost ytd which is going to be yeah a cost and then the percentage difference which is this one here which is going to be a percentage so i'll go and wrap that around these and then we'll get back onto it okay so i have wrapped a format statement around the cost the budget the cost versus actual um value and the cost versus actual percentage and we can see these here um let's test this and see if it works perfect okay so now we can see that we've got nicely formatted um row headers and we've got nicely formatted text here which is um well it's formatted in the actual query itself because of course we update the query there so excellent that'll be um that's the real building blocks for our table that we're going to go and create in the next step okay so the next step is to create an html table so let's add in an action here and we will search for html table and there is an option to create an html table if you spell it correctly there we go create html table and we're going to create from the output of this select statement here okay so that is pretty much that statement there but we do need to go and format that table okay so let's run it and test it and just see what we'll get anyway okay so i've expanded this open here and we can see that we've got our table and it's looking pretty good however we want to make this table look a little bit prettier we're going to put a colored bar a colored head on the top of it and put some um basically some lings either side of it i mean this is this is great i mean this is fine it's worked pretty well but just so that you can actually understand how you would format it i'm just going to talk you through that step okay so we're going to go and format the table so to do that i'm going to add a new action here and this is going to be called a compose action we're going to add a compose action so date operation and all it does is really let you create a chunk of code that you you can use later on okay um so in this chunk of code we're gonna have a style and we're gonna have the actual table okay so now we've got the table here so it's the output of this create html table is going to be the table we're going to use but prior to that i want to create a style now to create the style if you are competent with html and you want to just create the style then you can go ahead and do that however you can use a style generator like this one here now it seems to be that you do need to use this table table th table td for the the rows and the cells etc so this one works really well and you can just basically customize it how you want with the header colors etc now i'm going to just use this one here a blue background dark headers were the actual rows here now it doesn't really matter if you add any text into here because the only thing we're going to copy is going to be what's in between this style tags here okay so just get a copy of that and then ctrl c and we're going to go back in here and we're going to paste that into here okay and then at the bottom i am going to add in some dynamic text and it's going to be the output from this create html table okay i'm going to go and save and check or save and test that okay so we can see that that style has been applied to this output but of course actually we haven't actually rendered anything yet um but we'll test in a second but yeah that's all there and then here's the table here without information it's just one massive line here which is fine but don't eat all about that that's just the output from them from this control statement so let's go back in and we're now in the position where we can actually start to create an email that we can embed this table into so add an action and we're going to go and send email and the one we're going to use is this um outlook office send email now it might well be that it's v3 or even v4 depending on when you're going to be looking at this video but this is the latest one here and you could use it for google mail or any other options you've got for sending emails so let's choose that and then in here i'm just going to send to myself obviously you can send it to any name you want or names now the subject is going to be alert now the maintenance cost year to date is and now i want to embed a value in here which is this target value so remember right the very start we captured this year-to-date cost versus um budget percentage value so i'm going to put that in there however what i want to do first of all is i want to format that as a percentage okay so if i just put this target value in here remember the target value was just 0.00 you know it was a percentage basically so this is a second option for the actually current for formatting this number so i'm going to go to expression and i'm going to type in format and it's going to be format number open brackets and then go to dynamic content and i'm going to put the target value in here and then comma and it needs a format okay now the form i'm going to use is going to be a p now i'll leave a link below to the the options for formatting using this um power automate format number function because it's different to the format function that's in the query that we had earlier but you've got the two options and they're just slightly different formats there but p will just format this number as a percentage so once that's done we can click ok and just make sure that's come across okay it's fine um and i'm going to go and test this before we start writing the rest of the email okay it's now working because we need to put something in the body here so let's just actually go and actually stick in the table here so i the maintenance is cost and i'm going to just go and get this text here and i'm going to copy it use it again to add it into here as well the means cost is whatever percent above the budget for this time of the year okay and then i'm going to put in here the table so the table is going to be the output of the compose statement so it's now this html table there because you're going to get a table but it won't be formatted with a with a nice header and the actual um the um the lines etc so we're going to use the output of the compose statement okay and i'm going to put the power reports here and i'm going to go to the power bi report i'm going to copy that i'm going to add a link called maintenance cost versus budget report and then stick that in there and we'll open it in a new window and add it in okay end in an update node right okay doke so let's go and save that and let's go and test it and see what it looks like so it's good it's good this goes as far as this email and click to download now we don't need to click to download because i can go and visit my emails so let's take these in here and we can see i've tested it loads of times but here's one here alert the maintenance cost year to date is 2.76 percent should probably have expanded that to say above target um the mains cost is 2.76 percent above tar above the budget for this time of year and then here's the results here all nicely formatted with that format style that we used and then here's a click to the actual power bi report so everything looks to be in order and um we've got a nice um a nice report there that somebody who's out and about and can look at it on the phone or look at it on the screen and then decide if they want to go and visit the actual report here and um and get more details of what the what the actual issue is and investigating a bit further so hopefully you found this useful hopefully you'll be able to find an application for it in your organization loads of possibilities here and obviously loads of things you can do you can send a teams message or you can um you can send something to slack or whatever it is it doesn't need to be an email and you can still embed that that that powerpoint or that term that table okay so if you found this useful it's always much appreciated if you give the video a thumbs up and if you want to keep up to date with the latest videos then hit the subscribe button and you'll be notified whenever i release a video which is my less every week thanks for watching and i'll talk to you in the next video

Channel: Jason Davidson

Views: 21,870

Keywords:

Id: tbNvNk11sXM

Length: 25min 12sec (1512 seconds)

Published: Sun Sep 04 2022