Transform Excel Data into HTML Table and Email: Power Automate #PowerAutomate #Outlook

hi there in today's demonstration i'm going to show you how we can build a power automate flow that will take an incoming excel file with a table convert it into multiple tables that we can then send on via an email so this particular example has a table of data in excel and we have a manager who has potentially multiple clients we are going to filter on that data retrieve the distinct client names create individual tables for those clients and then send them those html tables as part of the body of that email so i do have a very similar video where i've done this i've done a filter to retrieve the rows relating to a particular manager but then that data includes every client in a single table the difference with this particular solution is that we're going to go and split those tables into unique individual tables based on the number of clients that a manager might have so if that's something that interests you please make sure you like and subscribe and without further ado let's jump into the demonstration so here i am in power automate and the first thing i need to do is to list the rows present in a table and i've saved my particular file onto one drive so i'll just go ahead and quickly fill out all the information relating to the location of my client data now in order for this particular solution to work we do need the data to be stored in a table but if you're maybe receiving an email with an excel attachment or a system is generating a file that you don't have a table saved within you can go and have a look at one of my videos that i will share a link to that will show you how you can add a table into a file automatically using power automate and office scripts so the first thing that we need to do is get a list of distinct clients and we're going to do that using a select action so taking the value as part of the from field here we can then change the select into text mode which will allow us to get an array of the individual client names so we can just go ahead there and select the dynamic value for client names so this will retrieve every single instance of the client name and if we have a look here as i'm highlighting we will get all of these client names in an array that's not the distinct list of clients so the next thing we need to do is use a compose action and the union expression and by using the union we can repeat a union on the output of the select and by including this the select body there twice we will retrieve the distinct values from that array so this output here will give us the distinct client names so now that we have the distinct client names we want to loop through each of those client names and create the html tables and the way i'm going to do that is using and apply to each i'll go ahead there and insert my apply to each and as the input to the applied to each i want to insert my distinct client names and then the first thing i need to do for this is to filter my original array of rows from the excel table so using the filter array action and inserting the value as the from we can then choose to perform a filter based on our client name which we can pick dynamically being equal to the current item and so what that means is we're going to go through all the rows in our excel table have a look at the client name and see if it's equal to the current item and the current item is based on one of those distinct values so this loop will loop through all of the distinct client names and one by one will perform a filter to retrieve just those rows that relate to each of those clients and so the next thing we want to do is create an html table and the reason i'm doing it this way is i wanted to avoid nested applied to eaches and try and keep this flow as efficient as possible so i've inserted my create html table the source of that html table is going to be based on my filter array so i can go ahead and pick the body now there are multiple columns on this table but i actually want to include maybe the first four columns so i'm going to go ahead and enable the manual or custom mode here and insert my column names so i'm going to go for the client id i'd also like to retrieve the vendor name i would like to retrieve the due date and also the tax amount and these are all values that are available on my excel table and i can retrieve them by constructing some expressions now all of those expressions will begin with item open close brackets and i always include a question mark because if that object or key name doesn't exist we won't get any errors we'll just get a null value and then i put in the square brackets and the single quotes and this is where we need to paste in the names of our columns so i'm looking to get the client id so i can just copy that client id there and paste that name in and then i need to do the same for the vendor so again i go into the expression builder type in item open closed brackets and single quotes and i'll maybe go ahead and just copy that expression to make it easier for the next lot and if i just hit ok and again i'll build the next one out and hit okay and again build out the next one out and hit okay i can then go and fill in the various names into these expressions so it was vendor name if i just go and copy the name of that jump back into my expression for vendor name and i can paste that in there similarly with the due date i can copy that jump into the expression for due date and paste that in there and then finally tax amount again copy that jump back into my expression and complete that too so now that's given us a bespoke html table for an individual client and obviously if there's five clients this will loop through five times so the next thing i need to do as part of this apply to each loop is to create an object and i'm going to do that using a compose now the purpose this object is to hold my html table and an html table per client and with those objects we're going to create an array so that we can then filter that array for the tables that relate to each of our managers so as part of this object first of all we need to create the opening and the closing brackets which are our squiggly brackets i like to call them and i need to first determine our manager so i'm going to insert a key known as manager and then i need to insert a semicolon followed by an expression so the expression that i'm looking to use is based on our filter array because if we think about this applying to each loop at the moment we're looping through all of the clients and then we're filtering that original list of rows based on that client name so we know that everyone within that filter array is for a particular client and therefore a particular manager and i'm going to use the first expression to retrieve that manager name from this filter array so i'm going to go ahead and type in first open and close brackets and then i'm going to select the filter array action and with that i then need to add on the value for manager so at the end of that expression i can put a question mark open and close brackets single quotes and the name of our manager field is manager and so that will retrieve the manager based on that filter array because ultimately it will have all the client rows and the manager's name will appear several times but all we ultimately need is the first then after inserting a comma after that key value pair i want to insert the html table and i'm going to do that using both raw html and some dynamic values so this time unlike the manager's name which isn't in double quotes this string will be in quotes and the first thing i want to do is to include a header tag so h1 and this will just enable us to have a header at the top of each of the tables that we create and that header is going to be based on our client name now we know already that we're in an applied to each loop and the current item is equal to that client name so i can go ahead and insert that into my html and then all i need to do is insert the closing tag for the header after that i would like to create a new paragraph so i'm going to insert a p in the tags here and then i want the output from my html table so i can go ahead and select the dynamic value from my create an html table and finish that off with the final double quotes so that allows us to create an object of tables for clients and i'm just going to rename that as my object of clients now there's a little trick that i use quite often through my flows and that is what's known as peter's method and that allows you to create an array based on a compose that's within and apply to each and we can do that outside of our apply to each by creating a compose action here we can go into the expression builder again type in outputs and we need to type in the name of that array that we have in the apply to each so if i just open that up again and remind myself i called it the compose object of clients and so the expression that i'm looking for is outputs and then in single quotes compose underscore object underscore of underscore clients and if everything is good we should be able to hit okay and we can save and test and see what we've got here but ultimately what we should have is an array of tables with the manager name as part of that object so just let that run through so if we go and have a look at that compose and just have a quick browse at the outputs we can see that we have a series of manager and html key value pairs we have jason mike who's our first manager who's just got one html table then we've got mike smith who happens to have two html tables and we've got marydo who also has two html tables so the next part of the solution is to go ahead and grab the distinct manager names so very much like we performed earlier in the flow we need to do the same now for our managers and what i'd like to do for forget is to rename this compose action to html tables array so i'm going to insert another select action and as before i'm going to insert the values from our excel table and then turn on the text mode which will allow me to select the manager field and so this will give me an array of all the managers and literally all the managers that we see highlighted on the screen here of course i want a distinct list or distinct array of those managers and i'm going to use a compose action with union to get those distinct values so i'll go into the expression builder put in the expression union and select that second select action twice in order to get those distinct values and we'll just go ahead and rename that so that i can recognize it later on so this is my distinct managers array so now that we have that distinct managers array we're going to loop through all of those managers and filter our html table array that we created so i'll go ahead and insert my apply to each as an input we're going to take the distinct managers array and then we're going to filter on that html table so we saw as part of the flow run all of the html tables had the manager as a key value pair we're now going to use this filter array action based on that html table there it is and we're going to check to see if the manager name is equal to the current item because we're of course looping through all those manager names so we go into the expression builder insert item open close brackets question mark square bracket and single quotes and just to remind ourselves the name of the manager column is manager so i can go ahead and copy that and paste that into my expression and it needs to be equal to the current item and it's not the current item of the original apply to each it's the current item of our second apply to each so this will now give us an array of the manager names and table names that are equal to the particular distinct manager name that we're currently looping through but i just want the html tables now rather than the manager names as part of that array and this is again where we're going to use select and that text mode so creating another select i'm going to actually rename this to select html tables for manager and we're going to insert the body from our filter array 2 go into text mode and we're going to need to construct another expression and that's going to be based on the key name of our html table so when i created that object in my original apply to each i called the key html table and so that's how we'll call that html table as part of this filter array action and therefore the select so that didn't make any sense at all if we jump up to our original applied to each and look at that compose where we have the object of clients you can see there that i created a key called html table and that's why i've created an expression here based on that same name because we're now filtering on that data and just retrieving the html table from our object so now that we have an array of html tables that are relevant to that particular manager we can now look to combine those html tables to create a string which we can then provide to our email action and before we do that we need to potentially join those strings based on another string and the easiest way to do this is to include another compose action so this compose action i'm going to call compose a return and i quite often use this if i'm joining strings maybe results from a microsoft form and it's in an array and i want to join it by a return i would go and create a compose with a return line like you can see there but because we're working in html today i'm going to use the br tag which will add a couple of extra lines into our html and so finally i want to compose my my tables again using a compose action and i'm going to use the join expression and so join will take an array and join on a string and so we're going to take the array from our select html tables and we're going to join based on that compose return which is our br tags so i'll go ahead and select my html table i'll put a comma after that and then i'll select my compose return which we can see there and i'll go ahead and hit ok and all that's going to do is join all the html tables based on that br tag so if we had three html tables we'd end up with html table the two br tags an html table two br tags an html table to be our tags and that's how the join expression works so before we finally get to send the email the last thing i'd like to add is just a bit of styling i'm actually going to copy and paste in a styling compose action that i've got from another email but it includes the same styling that we saw at the beginning of the email and it's all based on free html content that's on w3schools you can go ahead and grab similar styling if that's what you're looking for and the reason i put it into a compose is again it makes it very easy for when we send the email using the send an email action to just include that styling as part of the content so i'm going to specify myself as the recipient now obviously there are three managers in this case and the emails are in the data if we go back here and have a look at the original excel file we can see we have a column of email addresses and i will show you after i've run this flow how we can retrieve this email address and extend this solution further but for now i've put my own email address in there subject here are your tables when it comes to html we need to enable the code view and then by doing so we can insert some some more text here are your tables and then we can simply include the styling and also the output from the join which happens to be in my compose called compose i should have renamed that so that's the solution finished i'm going to go ahead and save it and test it and of course based on the number of managers i should get three individual emails and some of these some of those emails should have one table and some of them should have two if we jump across into my email i can see them all ping in there quickly so the first one there just has the single table you can see the header that i included as part of the html in the object that we constructed and our first apply to each the styling which is based on that w3 schools if i jump onto a different email here you can see i've got two separate emails based on individual headers you can see the two return lines which are based on those br tags and if we jump into find email final email you can see again we have another two tables based on the data relevant for that manager so what if we wanted to send the email to that particular manager rather than to myself well if i jump back into edit and where i really should have included it was part of this compose object that i did here and i can simply insert email and we can go and construct a similar expression to this one here so again using the first expression we will go and grab data from our filter array and at the end of that expression we can put the familiar square brackets and single quotes and go and grab the email field with a copy and paste and paste that in there and hit ok not forgetting we put that comment in so we get a valid object and so as well as retrieving the manager name we've also now got the email address so where can we pick up that email address well if we jump down onto this second apply to each we're of course filtering um where's it going we're of course filtering based on the manager equaling to current item that will then retrieve all of those manager names emails and tables that relate to that unique manager so their email address is going to appear several times through this filter array action what i can do is construct an expression based on the first result from the filter array and retrieve the email field so i'll go into the add dynamic content into the expression builder i'm looking to get the first from our second filter array there we go and again using question mark square brackets single quotes and stick email in there hit ok that will now get us the email address based on this particular manager if i go ahead and test that of course i will get bounce back emails but we'll be able to see that these unique emails have been sent to those individuals so you can see the flows run successfully if i can have a look at the history i can go into send an email and you can see here we have an email to jm test and if i jump on to the next one it's an email to mi and test and if i jump on to the third one we have ma test and if i jump across into my emails i have got three delivery failures based on those emails to those individual email addresses that were retrieved from the original excel table that we have here so that marks into the demonstration quite an interesting use case way beyond the original use case that i did where we had the single table and it's ultimately the most efficient way of retrieving the data rather than getting into nesting applied to each's we've broken that down into two individual apply to eaches using the select action in order to retrieve either the manager names or the client names and then combining that with the union to get the distinct client names or manager names and then by looping through those individual arrays of the distinct values we're then able to able to do some filtering produce some html tables and also then subsequently filter on that array of html tables to then create that unique email address so hopefully you've enjoyed that demonstration and if you haven't before please make sure you like and subscribe and leave your comments below it'd be great to hear from you thanks very much for watching and see you again sometime soon

Channel: DamoBird365

Views: 37,140

Keywords: Power Automate, Excel, HTML table, summary, distinct managers, data, Microsoft List, workflow automation, business automation, Microsoft, Office, productivity, data management, Excel data automation, Power Automate solution, Excel table input, distinct values, client data, manager data, DamoBird365, Power Automate Tutorial, Power Automate How To, Power Platform Tutorial

Id: pwJ73jFEtDQ

Length: 23min 52sec (1432 seconds)

Published: Sat Mar 05 2022