How To Automatically Send Emails By Linking Power BI And Power Automate

[Music] hello everyone henry and tim here from enterprise dna what we're going to go through today is we're going to show you how to connect power bi and power automate one very simple use case of that is if you have a power bi report that lists for example customers clients uh even members of your own team in a list and you want to be able to cycle through each one and do something for each customer client or team member for example you want to send them an email what you can do actually is you can connect power bi to power automate where you first in that list you highlight the records that you want that action to occur for and then you click a button and then a power automate script uh basically gets triggered that either sends an email or perhaps makes them a file something like that for each customer client or record now when we when tim and i were first tackling that it's fairly easy to actually do that and tim will walk us through but the error that we had come up with is sometimes you can have users who click the button before actually filtering to specific records and let's say if you have a list of all your team members in your organization and let's say if you're part of a 10 000 or employee company you don't want to send emails to 10 000 people so what you want to have in your power automate script is a way to have a threshold where if someone basically wants to send an email to a list of people from that list they need to filter it to a list less than 10 20 30 it's up to you but you need to have that stop there otherwise you end up sending thousands and thousands of emails unnecessarily which kind of blocks your outlook system and all that that uh that tim will generally talk about um so yeah so happy to have everyone here let's should we get started tim let's jump right into it happy to be here great all right so let me uh let me share my screen all right so let me jump right to so uh just following on uh henry what you were talking about here's a good example of uh let's say i've got a list of um you know people of some names maybe got visuals over here but for purposes of this i've just got a real simple table now in my example i'm really only showing six names but you know in your report like you said you could have hundreds uh you know thousands of records in this table and uh the great thing about power automate is using that functionality to send emails automatically uh rather than having to you know send it manually however um as i'll show you gotta run into some potential issues so i'll talk about that but let me just kind of quickly show this just as a quick example say here i i've got a um i've got my table and i want to send just an email to my sales reps in the east so i've got two of them here i've got melissa and i have sam so i've just highlighted them and i have a button up here this is a power automate button that i've connected into my report and i just trigger it and off the email goes now in my situation i'll have the email sent to me because uh just so that i could show this and if i quickly move over to my inbox here are the two emails that were triggered from power automate from the power bi report and now were sent to me and then um and obviously these would have gone to the user the issue is let's say as you were saying i didn't filter this and i sent this so a couple of issues that i have experienced and i want to kind of highlight here before i walk through sort of setting up the email process and then putting in that uh you know that conditional uh stopgap measure that i like to refer to it as is so a couple of the issues is as i said here the first one is we've sort of touched on when you have a table that has lots of you know hundreds or thousands of records it really can clog up your email system the other thing that uh people may not be aware of is if your table has a hidden filter in it uh but it's not the same filter that is in your your power automate button you can you can have some uh unforeseen results so for example if i were to you know if i put this over here right that's what i was looking at and i see here that i have a filter just on this table and i look at this filter and there is no filters um you could run into that so we're not gonna touch on that today but again this sort of like something that i i ran into one time and it's just something nice to point out right so let me just walk right through what i did uh first i'm gonna kind of walk through setting up the sending the email and then i will throw in the what i the technique i use to help with that sort of um prevent lots of emails going through so i've got a second page here so here's my table and i have a let me go ahead and um add over my power automate this should be a standard visual that is in the um that shows up in your visuals and the first thing it's going to ask for is fields so every time you pull this over it will ask for fields now it does not have to be all of the fields that you have in your visuals it only is it's the what's the data that you need for your flow so in my case i need the email address uh i'm gonna have the name because maybe i want to add in the name maybe i want to add in the title all right so what i'm going to do is i'm going to add so i'm going to move i'm going to add into my visual my name uh i'll do now i i've actually got two email addresses but just for demonstration purposes um this is the one so that as we go through the process that will be sent to me and i'll add in title because i want the title um but again it does not have to be these i could have other other attributes now that i have that set up and i have these this data in here i can just go up here to the ellipsis and then do the edit now this will send me to power automate one thing also to be aware because i know i've gone through your your power automate series on enterprise dna which is excellent is um you in order to access the uh the fields here you actually have to go through um you have to go through the the uh this process here versus going you can access the flow once i've saved it you can access it in the power automate application itself but it's a lot more difficult to actually access the um the dynamic fields in power bi so i tend to do a lot of my development here but you just so you know you can do both got it so this is a power automate shell within power bi it's much easier to develop the flow here rather than opening up a power automate web browser and doing it there okay correct great and i will i'll kind of i'll show that to some extent so let me go ahead now i've got a couple of flows already set up um that the one i tested it's it's this one down at the bottom but let's go ahead and create a new one so that's what i'll do and it so start from a template or instant cloud flow and in my case i want the instant cloud flow it'll automatically add this power bi button clicked so now i'm going to go into my connector so i know i want to send an email so there are a couple of options you have and i'm just going to touch to those so when i type in send an email you have the v2 and the v3 my understanding and my experience with the v3 and correct me if i'm wrong is this will be sent from the power automate service itself and then the second one this will actually come from the user who built the flow it'll come from their email their their email inbox yep that's correct now one thing to be aware of is what i've what i understand is when you use the send an email notification through the power automate you are limited to i believe it's 100 emails a day so that right there could be a built-in um you know approach uh however if you wanted the email to come from it to come from you then obviously you have to use this version which i'm going to do so i simply do that it asks me for the name and i'll add my dynamic content and i always forget this that you know i think you always have said this to in your trainings where i'm not missing some of my fields where i i want this now in my case i only needed there's there's only two email addresses that i need so i have the user email which is a default one which this one does appear and i will actually access that later i'll show you how i use that uh and then here is my how here's the data field that i've added so i simply put that in got it so just to clarify the user email well the the first dynamic data that we saw over there is the user's email address the person who actually clicks the button right correct got it okay we don't want that for this one we only want the power bi data email which is the email in the the table got it okay that's correct now we're gonna we're gonna use this email later on i'll show you that when i i go further awesome and because and um and i think and you know this go having gone through your trainings because the data that is coming from the power bi report has multiple records it automatically converts it to the apply to each control perfect so i just have to reopen it up there's my there's my two i added my subject so let me just add um this is an email for and i'll just put in the users let me just put in the name again just adding more dynamic content and i could say you know deer add in their name here is your email and if i save that um let me give it a name here i'll test so this right here is the basic principle which is to and all you need to do to set up an email thing so if i save and then i apply this if i save and apply now when i go back and i run these tests it's going to go ahead and send all the emails awesome now i think we put into your issues i'm guessing right we run into the issues because what happens now if you have a thousand rows and all of a sudden it's going to cycle through the apply to each a thousand times so the way i came up with this is i want to be able to tell the flow if it is above a threshold that i determine so say i want the threshold to be i'll just say uh in this case since i only have six records i'm going to say three if it's above three if it's uh if it's above three records that i've selected don't stop the flow that will and then send a send a thing to the user so what i need to do is identify how many uh records there are so the way i approached this was well let's just create a variable so i'm going to go and add my action i am going to find my variables and i will first step is i'll initialize it so let me call this i'll just call it record count i make it an integer because i want to just have a count and i'll start with zero okay so now that i have my variable initialized what i want to do is now increment the variable so that'll help me determine how many records were selected uh when i clicked on the power bi the button so um easy way to do that is i'm going to add in an action and because uh this is i want to cycle through uh everyone i'm going to go ahead and use the apply to each so i'm going to go here to the control and i'm going to select apply to each and then what i want to do is the apply to each to i want to cycle through each one of the records and i can choose really any of the my power bi field so i'm just going to choose name now now you'll notice it'll throws in an extra one because there are multiple so let me i'm going to delete that and now through each one i'm going to add in an action of i want to increment my variables so let me go here and in the variable there is the increment variable option so i will choose that and for my variable i will select the record count which i initialized previously and i want to increment it by a value of 1. so now i have that and what that'll do now is it'll cycle through every single record in my in my in my set in my data set and then it'll it'll increment that um and at the end i'll have the total count of how many records there are and this is what i can now use in my conditional statement got it and tim what's the purpose of doing two different loops you have one loop that goes to each record and increments the variable and then the loop that actually sends out the email so the reason you have to use two the reason i used two in my example is i wanted the first loop this first loop applied to each is the loop that determines how many records are in here once that's finished it'll tell me at the end that if i've selected five records it'll loop through this cycle five times at the end my my variable record count will have the value 5 because i've incremented it that many times you have to do that first because once i get i want to be able to use that in my condition which i will i will i will jump to you right now got it okay that makes sense so now i'm gonna go ahead and close this for just to make it easier so now that i've gone through this step and and created my variable my variable that i've called record count let me add a condition so this is where i want to go into the control and i want to add a condition and where i'll pull in the okay so i want to pull in my my record count variable so i pull this in and if it is i want to choose the equal to less than or equal to and i'm gonna in my case i'm gonna use three because i only have six records this could be three ten twenty whatever the uh whatever you want right what i want to do is to say if the record count my variable which i i determine the value from this loop that we just talked about if it's less than three then i want to go down the yes path if not then i want then go down this which this will be my my where i cancel the flow so here's the flow that i want to have um the step i want when it's when it's less than or equal to three records and all i have to do here is since i've already created i just move it and now it's there it'll send the email and i all will be good if it is if this condition if this variable record count is greater than three so say i select four more records then it's going to go through here this is where i i'm going to do two things first of all i want to alert the user no so i'm going to do my send an email and i'm going to choose the v2 again now this is where we had talked about earlier the field in this power bi this was the user email so again this is that field now that comes into handy because it's who triggered the club got it okay that makes sense so i'm going to choose that here's my the flow was cancelled um just to say the flow was cancelled selected and this is where i can pull in my my variable of course it doesn't show so i have to search for i i learned this it took me a while to figure this one out uh records the limit is i think in my case three okay so now i've done that so let me save and apply and the other thing i was going to point out is i've applied it because it says here um and i'm gonna go back to my report um let me go ahead and test it first let's make sure this this runs so what i've got is i'm gonna go ahead and select let's say in my case i'm gonna select let me select two i'll just select two records here and we run the flow it's triggered and let me jump over and there are the emails so there's the henry there's your email and of course in my example in the real life situation have been sent right to you right but here are the two emails now let me uh let me just select all and this should trigger that condition which should cancel my flow there's my email the flow was cancelled you've selected six records the limit is six perfect so um one one uh one quick thing i wanna point out is um when you do when you go to attach a flow uh unfortunately i don't think there is a way that like this that here's the flow send it send email tests this is one that is applied to my to that button but there's no indication here so it's just something to be aware of got it okay okay um do we have time for one quick bonus feature uh yes let's do it okay so i want to throw in one little quick feature here um let's say your users don't have access to the flow and you want to give them the ability to choose the record limit so in my case i want to i did three let's say i want to say that i want to give them the option to choose five what i've done here is i've got a little slicer all this and then what i'll have is i'll show you as i embedded the flow and next to another variable that you can now make this selection be that limit um and all i did here was i've just created this is just a simple table this i just created a simple i called it max selection count and this is my what i'm using for my slicer so i've got 135 and 100 i just type them in that that column max selection is what is is in my slicer this is just a simple slicer and i have created a measure i just called it selected max records and all i'm using is the selected value function and it's just selecting what is in this slicer now you do want to make sure it's it's converted to a radio button so that the user selects one and now i've just in my flow i've added that measure so now what i'm sending to the flow okay this number and if i quickly i i already got the flow done up so we're just going to show that real quick i have it right here so let's edit that um here's the first variable i've initialized i just created a second one called max records variable and again make it an integer and now i do another loop very same thing i loop through that same step but instead of doing incrementing i'm just doing the set variable and here's my measure that's selected max records all i'm doing is setting that measure setting my variable here the same value of that that of that measure whatever selected so it just loops through the same thing every time but again at the end now my max records variable is set with that that measure that i've created and in my condition all i've done is instead of having it hard coded i've just plugged in my max records variable so now it's dynamic and then i've just added it in my email i can just say here the number selected was here's my record count and then this succeeded so so again just a little extra way to add in some additional variables so great yeah no that is awesome so what we kind of went through is a way to take your power bi reports to the next level and specifically you can have a table and click a button that basically executes a process for example sending an email to each record and then i'm sure tim the reason why we also went through including the stopgap is i'm sure you've done this in your reports and then accidentally sent an email to 10 000 people and they got in trouble for it yeah this is a new technique i'm going to start doing because again it's you know i always want to be careful there so uh hopefully this uh this people will find this useful yeah this is great let us know if you have any questions in the comments below take care everyone thanks hey everyone thanks for tuning in to enterprise dna tv if you enjoyed the contents covered in this particular tutorial please throw the video a like it really helps us and we really appreciate it also don't forget to subscribe to the enterprise dna tv channel we have a huge amount of content coming out all the time from myself and a range of content creators all dedicated to improving the way that you use power bi and the power platform lastly check out enterprise dna's website plenty of resources and further learning that you can access very easily all the best take [Music] i

Channel: Enterprise DNA

Views: 33,709

Keywords: Power BI, Power BI DAX, Enterprise DNA, Power BI Tutorial, Power BI tutorial for beginners, DAX Tutorial, DAX tutorial for beginners, power BI desktop, power query for power bi, learn power bi, learn dax, learn Power Query, M language, Power Query M code, Sam McKay, Power Automate, Power Automate Tutorial, Power Automate for Beginners, Learn Power Automate

Id: lIZ3OuU3hsk

Length: 23min 8sec (1388 seconds)

Published: Mon Apr 04 2022