Transform Excel Data into HTML Table and Email: Power Automate

Are you tired of manually summarizing excel data and sending it to multiple managers? In this video, I'll show you how to take an excel table as input, get distinct values such as manager names and clients and send HTML table(s) as a summary to each of the distinct managers. Not only that, but you'll also learn how to split the data into individual clients and send different tables to the same manager. This powerful technique can also be applied to a Microsoft List.
This is a great solution for those who want to automate the process of summarizing and sending data to multiple managers. Whether you're working with excel tables or Microsoft Lists, this video has got you covered. Don't miss out on this opportunity to learn how to take an excel table as input and send HTML table(s) as a summary to each of the distinct managers using Power Automate. Make sure to hit the like and subscribe button!
00:00 Intro
01:12 Start of Demo
01:56 Distinct Values using Select and Union
03:06 Create HTML Tables via Apply to Each
06:50 Creating an Object and therefore Array of HTML Tables
11:55 More examples on Distinct Values
12:50 FIltering on the unique tables based on Distinct Managers
15:37 Joining the Tables
18:03 Send an Email
19:06 Demo of solution and the resulting Table(s)
20:03 Extending the solution to include unique email addresses
22:49 Outtro
Add table to incoming Excel File attachment using Office Scripts:
• Add a Table to an Exce...
HTML Table Styling www.w3schools.com/html/html_t... Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕

[@jamesarthur2360]

This is what I've been looking for. You are the best. Thank you so much

[@mariolopez-od7pr]

Thanks Damien!, Another great video!, your explanation is so clear and very detailed. This is what I'd call a complex flow, but you make it so simple. Thanks for sharing your knowledge and expertise.

[@pbiqueryous]

Thanks Damien, eternally grateful for this knowledge. You are truly helping us solve real-world challenges which are troubling us to this day! You have definitely prevented us from project collapse! :)

[@DamoBird365]

Thanks very much for your kind words. I’ve just posted a link on the community tab of my channel where you can submit new video ideas. Feel free to reach out if there are areas of interest I could cover on my channel.

[@ivanbork4175]

Thanks Damian That was a great inspiration, and a great help Like the option to follow your instruction step by step, and then add my local changes to fit our needs in the end

[@iandobbs4920]

What a star! I’ve been struggling all day to find a way to automate emailing a filtered table of results from Excel/Power Query (where sensitive data is in the query but not in the table)! Delighted to have found your channel Damien - thank you so much! Can’t wait to try this out tomorrow. 👍

[@DamoBird365]

Oh wow thank you.

[@gabrielesabbatino2460]

Brilliant as usual! Thx so much for your work!

[@aatsw]

Top notched contents. Thank you! Subscribed.

[@pfcoop1]

Great video! I'm going to try this on an Excel Order form.

[@bradsbirds8502]

helped me do exactly what I needed!

[@badriruslan8797]

just the one i need right now. thanks for the video.

[@badriruslan8797]

can you help tp paste the ComposeStyle in the comment? I cannot get the style working as per required. i also try to add background color to even line, still cannot perform the task.. here is my code: table { border-collapse:collapse; width: 100%; } th,td{ padding:8px; text-align:left; border-bottom: 1px solid #DDD; } tr:nth-child(even) { background-color: #D6EEEE; } tr:hover {background-color: #D6EEEE;}

[@DamoBird365]

@@badriruslan8797 www.w3schools.com/html/tryit.asp?filename=tryhtml_table_hover

[@ThakralShubham]

Thanks Hopefully this will work in my case

[@evandrodossantosYou]

This video is a Hand Book Chapter for me. Thanks

[@DamoBird365]

Thank you. If you think of any other scenarios I could cover, please do drop me a dm.

[@bernielindsey8387]

Damien, Thank you much. Great content. For this flow, what if you wanted only one manager? I'm trying that approach after re-creating your flow, and thought I could input the specific name in the Filter Array instead of 'Current Item", but that does not work. I'm thinking a different step right after list rows present, but I want the same output (just one email and one manager from a long list)

[@DamoBird365]

Hey Bernie, I don’t quite understand your requirement? If you just want the flow to run on one name, you can perform a filter on the rows, under advanced, columnname eq ‘manager’

[@cryptomugen1315]

This was a fantastic tutorial. Love how you explained each step. Clear and concise. Question, I see that you grabbed the first entry for each manager which grabs the first tax amount for the corresponding client. Would be great if you could expand on this and show us steps on how we can get a sum total of tax amounts for each client.

[@DamoBird365]

There is an xml expression sum() which can sum the tags selected. docs.microsoft.com/en-us/previous-versions/dotnet/netframework-4.0/ms256160(v=vs.100)

[@DamoBird365]

Here’s a recent example from another Super User ABM kzbin.info/www/bejne/a4GqaZWgir2an8k

[@lucasemerich6109]

Great video!!!, i wanna to automate this process to run every time i modify the excel file, but i just can make it work, any suggestion?

[@michis.]

Great video!!! 2 things I´m missing. Due to my excel spreadsheet I´ve to use 3 filters at first. Is this possible? The 2nd problem is that excel with all listed items has to be attached in the email for every seperate person.

[@niiivee9388]

Exactly what I was looking for... Thanks for the video Damien. One question though, is it possible to do the same using excel files with more than 30000 rows?

[@DamoBird365]

It should be possible, please give it a go and report back. You could always ask on the forum too powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@ryanlucas1313]

Hi Damo - is it possible to replace the send mail action here with the process shown in your video on creating a draft?

[@DamoBird365]

I would think so. You can define the content type as html. There is a bit about it on my blog post www.damobird365.com/create-a-draft-email-in-outlook/

[@cvkealey]

The way you're creating those arrays ("Peter's method"?) - is there a resource (blog post, video, etc.) that explains that in more detail? I can see that it clearly does work, but I'm not sure why.

[@DamoBird365]

It’s tried and tested, Pieter Veenstra sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows just promise to come back 👍😂

[@phoenixkissme]

Thank you for your video. I have one question. Can the subject be dynamic as well?

[@DamoBird365]

Yes, absolutely

[@davidfelipe4629]

Damien fantastic video, I would like to know how I can preserve (Money format, date format ...) or put format for each field in the html that comes from my dataframe

[@DamoBird365]

Try out formatNumber(123,'£0.00')

[@TheZeidinho]

Thanks for the video. Unless I misheard, I think you may have confused the semi colon (;) and colon (:). Example at 7:33

[@svenm.3881]

Thanks Damian, that was really helpful; I have two questions: (i) would that also work if there is no 1-1 relationship between client and manager, e.g. if I‘m the manager of a sales team and I want a table by „product group“ (which could be sold by several sales teams) and the rows showing each sales person (+products sold and number of products sold) or would i need nested „Apply to each“ in that case? (ii)could I add „dynamic text“ to each table, e.g. if client AT1 Cabinet is selected, it add a specific text below the table, etc? Thanks

[@DamoBird365]

You would probably use a filter array? Then if you want dynamic text, consider an object to query it. The other options are another data source like a list (scalable) or a switch which will become hard to maintain. Check out the following for ideas kzbin.info/www/bejne/hnWcaWOpgLCrZqc and kzbin.info/www/bejne/a57WqZtrit1rers

[@svenm.3881]

@@DamoBird365 I'm afraid that I structure with the nested "Apply-to-Each". I have a table with the following columns - Line Manager Email, - Product Category, - Product, - Sales Date, - Sales Amount and I want to generate an Email that will send to each Line Managers an Email with HTML tables split by Product Group, containing the columns Product, Sales Date, Sales Amount; Each Line Manager can have sales from one, two or more product categories

[@DamoBird365]

@@svenm.3881 I think you’ll be best on the forum for this as folk can then see your logic powerusers.microsoft.com/

[@R01202]

Hi Damo, is it possible to use the 'List Rows Present in a Table' action with a dynamically named file? I am trying to automate line item approvals for a daily report, and each day's report is named 'DD-MM-YYYY daily report' (Also, it is a macro enabled workbook, .xlsm if that causes any issues). If it is possible, how would I configure the 'Select' action? The columns are always the same in each day's file. Only the number and content of rows varies. Thanks!

[@DamoBird365]

It is possible and I demo it in another video I think. Your file name could be dynamically constructed using formatdatetime.

[@surajdevasthali6107]

Hey, in the same flow if there are multiple person field in sharepoint then how can I pull their names and put in the final HTML Table in email body? also send them email? I'm getting Manager column blank in the HTML table as I have at least two managers for per record.

[@DamoBird365]

My latest video should help you Power Automate | Apply to Each | Why? | Arrays and Objects kzbin.info/www/bejne/mmqcg6Vrmc-Wg6c. A select action and a join on the people picker results.

[@tomproudman1344]

Hi Damien. Thanks for the detailed explanation, this is a very useful video. One thing I'm struggling with is how to get dates to format as dates rather than as numbers. I note that the dates in your Excel data are pulling through in the correct format without any formatting evident in your Power Automate flow. How are you achieving this, please?

[@DamoBird365]

Under advance there is date format? Select iso8601 instead of serial.

[@tomproudman1344]

@@DamoBird365 That worked, thanks very much.

[@WilsonC15]

Good instructional video. I received an empty output when I applied each filter array. I am not sure what is going on; it just did not work out. It took me ten hours to solve the issue. Finally Ahha...I discovered that I forgot to switch text mode at the "Select>map" so that the system is able to pick up the string! That is the key. No doubt I must thank you to your tutorial video and improve me as well from mistake.

[@DamoBird365]

Thanks for updating me 👍 well done on working it out and enjoy the learning.

[@dayeshs]

Hi Damien, I have created same flow as mentioned in tutorial but flow gets failed In filter arrar2 under apply to each2. Request your quick help.

[@KiLLERJA0]

Great video, helped alot, but curious if I wanted to add say color to the text how would I go about that? Like where would i enter the html color codes for the text?

[@DamoBird365]

You can explore ideas on w3schools www.w3schools.com/css/css_text.asp

[@AshBonarski]

Thank you for making this video! This helped me start to wrap my brain around html development. Question - would it be possible to use this same flow to be triggered when there is a new form completed and to only send the new response? I still want it to reference the excel table as I have the file auto assigning file numbers.

[@DamoBird365]

Are you writing the new response to excel and then querying it? My worry would be that excel can take up to 6 mins to update. A list would give you more reliable outcomes.

[@AshBonarski]

I included a delay of 30 seconds after the response is submitted to work around the issue. It’s working perfectly, other than sending out a table of every response to everyone who has ever submitted😅

[@AshBonarski]

But yes, I am creating a new excel row for every portion of the response in another flow, then querying it in this flow with the delay to notify the requestor of the automatically assigned number in the excel file. Just can’t figure out the last step of only including the newest response.

[@jitendrasonavane82]

Thank you, Damien, really nice tutorial in details. I am working on one project silmilar to this wherein in excel sheet ,there is one column called INVOICE DATE how could I add THIS Column in HTML Table, I am facing date conversion issue, please help and guide.

[@DamoBird365]

I’ve a video on dates. Maybe look at parsedatetime(). kzbin.info/www/bejne/r5OziKerZs5koJI

[@AlzD3mi4n]

Damien, could you please tell me the name of the video that you referred to at the beginning of this. I want to send a table with data relevant to that user. Thanks for your content!

[@DamoBird365]

Hopefully it was this one 👍 Send Unique Emails with HTML Table using Data from Excel #PowerAutomate kzbin.info/www/bejne/a57WqZtrit1rers

[@AlzD3mi4n]

@@DamoBird365 Thanks! I was watching that video but I didn't finish it, so I missed the solution I was looking for.

[@vanessabryant8920]

Hello! Terrific tutorial. what is the best way to include multiple dynamic tables in an email to multiple persons dynamically. in this video, there are instances where there are multiple tables for managers in instances where a manager has multiple clients. However, if i need to include 2 completely seperate tables with different data and headers, what is the best way to do this? will i need to complete all actions as described in this video twice? the headers will be different for the two tables but the managers and clients will be the same. Appreciate any assistance

[@DamoBird365]

Thanks Vanessa. Depending on the data, you can repurpose the sources using select and then filter them in the same loop. Build the html tables off of the two filters. Hope that makes sense? Albeit a bit generic I appreciate. Do you have the scenario posted on the forum?

[@vanessabryant8920]

@@DamoBird365 the data coming from two different excel files and two different tables with different headers. I have 100+ "clients" that need to recieve two different tables with dynamic information. I was successful following your steps to get 1 dynamic table and have replicated the steps for my second table. however, i am having issues merging both tables into a single "send email" action

[@naziahurrymun1617]

Hi Damien. We have followed your tutorial, even using your data set. We are having trouble at the Filter Array Outputs step. The email only shows "Here are your Tables" and isn't pulling through the HTML table. Not sure what the issue is, would you have any idea where we're going wrong?

[@DamoBird365]

Not easily, no. I would suggest you go back through the flow history. Check the various stages. See where the data doesn’t exist and then edit the flow at that step.

[@55SabreDog]

Thank you very much for this - exactly what I was looking for! My input (from Excel) has a numeric field that I'd like to display as US currency in the resulting email table. I've tried adding a variable, tried inserting Format Number function, tried formatnumber... much appreciated if you could let me know how to do this and where to insert the code. Save what little sanity I have left. Thanks!

[@DamoBird365]

Try format number? docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#formatnumber

[@55SabreDog]

@@DamoBird365 Would that go within the first "Apply to Each" in the Create HTML table function? I tried various iterations of item()?formatNumber(... ) but keep getting an error. Dang it all :-)

[@DamoBird365]

@@55SabreDog it would be formatnumber(item()?[],etc)

[@boudj73]

Thank you so much. I have a question regarding the header (Client Name), is it possible to hide it or put it in white color to make it invisible when the table is received by email? Thank you again.

[@DamoBird365]

You could build an advanced html table using a select and this would not include a header. kzbin.info/www/bejne/goLYnnaIZqdgp5Isi=gRev3dKUYihdxZXJ

[@Justbrowsingsoflo]

Thank you so much for the thorough explanation! Applying this concept to a weekly project. One issue I am trying to figure out is the final email shows the bracket and quote surrounding the html table. Opening bracket shows above it and close bracket below it

[@DamoBird365]

I’m not sure I follow you. Can you share an example?

[@Justbrowsingsoflo]

The body of the final email sent shows the open brackets and quotes before the html table and after it. Looks like the output of the select from filter object array that contains the html tables includes the brackets and double quotes. Wasn't able to post a link to the photo showing the email. Appreciate your time trying to help

[@DamoBird365]

@@Justbrowsingsoflo do you mean square brackets of an array? [ …. ] ? You could use first() or ?[0] and create an expression using the action with your html?

[@Justbrowsingsoflo]

@@DamoBird365 yes, array brackets. What ended up working in mine was converting the output of the select to text, wrap that around replace function to remove the brackets and quote. I haven't tested but I think that in your example, when you sent the select from the filter Array to a compose, then joined it to the tag compose, it may have automatically removed the array brackets. Originally I had just the output of select that was giving me the brackets. I tried feeding that select output into a compose and was still getting the brackets. Either way, all is well now. Appreciate your help and again thank you for the great tutorial!

[@PaulTaylor-xk2ti]

Such a good explanation, Damo is there a way to put the Managers name in the email body like "Dear , I tried but it keeps looping and send lots of mails ? Also do you do any work for clients, I would like this flow built for our company ?

[@DamoBird365]

You can’t use the dynamic value as it will put you in a loop, as you’ve identified. You can get the email from the filter with first(). I think I do something similar for the managers name? Drop me an email [e-mail] if you want to discuss.

[@kamacete]

Hi, thank you for the great video. I tried to change your managers, for another column, in my case, "Status", can we send an email just with a selected status? for example: "send email". thank you in advance

[@DamoBird365]

I believe that should work ok? It should be a lot simpler. You can either list rows and then perform a filter or filter from the list rows action.

[@kamacete]

@@DamoBird365 I will try, thank you very much

[@learntoautomate365]

Hello Damien, thank you for this video. I am trying to filter my excel based on the name of an employee (for example John). I would like that all the John rows to populate it into my html table. I am skipping the union action you are using but the flow doesn't work after the filter array step. What am i doing wrong?

[@DamoBird365]

I’m not sure to be honest. Maybe try the official forum?

[@russellkelly6034]

Very useful, just what I needed! I am facing one situation, what if there are multiple managers assisgned to one client. I would need to send a seperate email to each manager assigned to these shared clients. My only workaround so far is to create a helper column in excel combining the client name and manager name to create that unique list of client names. when the Compose Object of Clients is run it will include the header name of the manager and client name, is there a way to get the header to only use the client name as the header?

[@DamoBird365]

In your filter, rather than equals, you could use contains on an array of managers. If you list the managers in a column, you could use split() to create an array. Although saying that, contains works on strings too.

[@DamoBird365]

docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#contains

[@russellkelly6034]

I actually just figured it out. I simply just copied the first filter array expression from the line above and used that for the header, just used the client name (instead of using current item)

[@avinashmali3693]

Hi, i am not getting Dynamic content option, i am trying to send table from power bi itself using power automate inbuilt

[@davidadams421]

Very interesting, blew my mind but I'll be re-watching (a few times, probably!). I need to do something similar with data originating from my accounting software, FreeAgent (a SaaS application). Basically I want to send my clients a weekly timesheet. I've got the API query sorted (returns a JSON object) now need to manipulate that into HTML tables for each individual client. Have you done anything similar before?

[@DamoBird365]

If you’ve got an array, it’s just the same idea as the excel list rows returns data as an array.

[@pullan11]

Watched a few of your videos and they all almost seem to cover what I'm trying to do haha! I want to use a filter array to get my date column, then retrieve anything that is -14 days from that date. The output works perfectly but then I can't figure out how to get that output into an email! Do you have any videos that might cover this?

[@DamoBird365]

This might help? Send Unique Emails with HTML Table using Data from Excel #PowerAutomate kzbin.info/www/bejne/a57WqZtrit1rers

[@pullan11]

@@DamoBird365 just tried and that worked perfectly! re-worked slightly for my use case but incredibly helpful! appreciate your video and for responding!

[@kiko3117]

Excellent video! Just one question: I do not get the dynamic content/expression for the Map field in Select HTML tables for manager. Any idea why? Thank you

[@DamoBird365]

Do you mean no dynamic value? You might need to construct it manually? Something like item()?[‘fieldnane’] otherwise you might have to use parse json depending on the data source.

[@DamoBird365]

P.s. I tend to construct expressions manually JSON Array in Power Automate - Walkthrough lesson with examples #PowerAutomate #JSON #Array kzbin.info/www/bejne/bJ-thIiEnZpqb9k

[@kiko3117]

@@DamoBird365 thank you for your quick response. I meant that for the Select Operation I am able to add the correct dynamic value to the ‘From’ field but when I move over to the ‘Map’ field I am not able to put an expression like you did in your video around the 15 minute mark

[@kiko3117]

@@DamoBird365 disregard… I was in portrait orientation and that option wasn’t showing up. I moved my page over to my landscape monitor and it shows up now… thank you anyways!

[@bhartijaybhay9756]

HI @DamoBird365 want to generate Pie chart from Power Apps or SharePoint. How can we do that? Instead of Excel SharePoint can we use?

[@DamoBird365]

check out quick charts api as an option maybe?

[@will_will777]

Very good!!! Excellent teaching method!!! You have a 'subscriber' and a 'like'. Brother, can you give me a hint? I want to use your flow to bring data from Power Bi tables and not from Excel. Could I enjoy this flow? I only want to change the first and second step before getting to 'Compose'. I inserted in the first step: ''Run a query on a dataset' (Power Bi)'. Insert in the second step: 'Parse JSON'. I don't know how to connect the 'Parse JSON' step to the 'Compose' step to continue the flow. Can you help me with this first and second line? Thank you bro!!!

[@DamoBird365]

Parse json just writes the expressions for you. You’ll want to replace the excel value with the array of data from BI. Good luck 🤞

[@will_will777]

tks bro!! @@DamoBird365

[@binumathew721]

Hi, Thank you for the wonderful video and explanation. I am stuck in the Compose Object step after creating HTML table in the first "Apply to Each Row" I need to send the emails to Focalpoint (that is the name of the Excel table header). So I mentioned it as below first(body('Filter_array'))?['Focalpoint'] But I am getting null in the output But in the next line for HTML table, I am getting the correct value inside the header tags. What would be the possible reason?

[@DamoBird365]

Assuming you can see data in the history of the filter array, is there a value focalpoint in the output history. Flow will return a null where a ? Is used in an expression and no value is found.

[@binumathew721]

@@DamoBird365 Thank you for the quick response. Yes there are values for Focalpoint in the output history.

[@DamoBird365]

@@binumathew721 to debug, my recommendation would be add a compose containing the first(body(‘filter_array’)) expression. This will return your first object. Then check the history.

[@binumathew721]

@@DamoBird365 thank you for the response. I was using everything you said, but it was not working. So I decided to duplicate your table and follow the steps. The only change I did was to change the column heading "Manager" to "Focalpoint". It did not work just like my previous case. Then, I changed the column heading to Manager and it worked. I am not sure whether it is some sort of a keyword. Thank you!

[@DamoBird365]

Very strange but glad you’ve got it working 👍

[@ancyjose5560]

Hi Thank you for the video, I created an HTML table using power automate. I converted it into PDF. File having multiple pages... Is there any way to show the table header in each page .

[@DamoBird365]

If you’re using html to create a pdf, you need to split the html in div sections. Alternatively why not convert the excel file to pdf? kzbin.info/www/bejne/eYupfZ2vbc14rpI

[@shyambhadresa]

Really helpful video! Would you have a demo where instead of converting to HTML tables per manager, you create a csv/excel file for each set of data which is attached to the email? Thanks.

[@shyambhadresa]

any suggestions/guidance on this would be great!! :)

[@DamoBird365]

I don’t have a video on exactly your requirements. What’s your data source? Csv is easy as it’s a text file, excel is more complex but I have a video on how to create a new excel file.

[@shyambhadresa]

@@DamoBird365 Hi, My datasource is an excel file stored in sharepoint (I did want to directly query SQL, but I am not sure if that is possible) As a starting process, I would be okay with using csv! Would it be a matter of replacing the 'Create HTML table' with a task which creates csv files? or would I convert the html tables to csv?

[@DamoBird365]

@@shyambhadresa so many ways to tackle this and yes you could also query sql. You can also split a workbook into multiple sheets or books using this method kzbin.info/www/bejne/noiXaKFjiNmBi8U

[@shyambhadresa]

@@DamoBird365 I have done some intense googling but nothing yet - I'm pretty new to this hence the googling and of course the deadline for it! :D

[@marcoschade352]

Great video Damien, just what we needed. I have only one problem. The send an email fails and gives med the error message "Parameter 'To' cannot be null or empty.". So it says that "To" is empty, but i can see that both the compose object of manager and the filter array 2 finds all the right emails. What could be the solution to this problem?

[@DamoBird365]

What expression or dynamic value have you inserted in the to field? Check that value, it must be returning null for some reason. Maybe a typo? Expressions with ? will return null if the key is not found.

[@marcoschade352]

@@DamoBird365 i am using this expression: first(body('Filter_array_2'))?['Email - Kortansvarlig']

[@DamoBird365]

Check your filter array. It must have returned 0 results. You can see this in the flow history.

[@marcoschade352]

If I, in the "To"-field, choose to send it to myself everything looks like it should.

[@weierschatten3433]

This is the best thing I've seen done with PA yet! Trying to get it to work with SPO, got a mistake somewhere because the tables aren't showing up in the email and sometimes it wants the "field_x" instead of a headers name, maybe something to do with SPO.

[@DamoBird365]

Thank you. What you’ve described is the internal field name of SharePoint lists. They’ve got a friendly name and internal name. Often they match but if you create a list from an excel sheet, they are 1,2,3 etc. you can check the field name from list settings and clicking on the field. Look at the url in the browser 👍

[@weierschatten3433]

@@DamoBird365 Ah yes I‘ve heard that before. It’s true, I imported my list from Excel! I‘ll try to fix the flow tomorrow, but the outcome seems well worth it.

[@weierschatten3433]

@@DamoBird365 I think I'm stuck... The flow works, but I get my emails grouped by client and not by manager. The error already exists in "Peter's method", so I guess one of my filters at the start is wrong? Or could Sharepoint get in the way? Using "Get items" and "value" fields as equivalents to the Excel connectors...

[@DamoBird365]

It should work with any data source. I guess you must have create distinct clients, instead of manager?

[@weierschatten3433]

@@DamoBird365 I think I got the managers alright, the problem seems to be that one client of mine can be allocated to two different managers. I guess that because of the first(), it only gives me the first manager it finds :(

[@jassonsolares4200]

Hi Damo, I am working on a similar project, but instead of sending 3 different emails I would like it to just send 1. Have all tables in one email, instead of sending it to the different managers(based off of their clients), it will go to just 1 person(all 4 or 5 different tables). Which steps do I have to remove to make this happen? I've tried removing some steps but I've been unsuccessful. Thanks!

[@DamoBird365]

The easiest way would be to create a string variable and append each html table to the variable via the apply to each. Then send the email using the variable output.

[@jassonsolares4200]

@@DamoBird365 Thank you! This worked perfectly.

[@Lamouettekamikaze]

Is it possible to use the dynamic table in a word document?

[@DamoBird365]

You could use a repeating control which is premium, yes. kzbin.info/www/bejne/onO7hKZ7ftGKpc0 or check out kzbin.info/www/bejne/gmeanmutnMlnd80 for various document options

[@thanatK]

thank you this VOD is very helpful, i have one more question Can i replace all text in the table vid power automate expression. example replace all '5440' in the table with 'KZbin' replace all '5441' in the table with 'Google' replace all '5442' in the table with 'Facebook' thank you

[@thanatK]

just put replace(item()?['header'],'5440','KZbin') in the convert to HTML Table step but for mulitcondition how to done

[@DamoBird365]

replace(replace(replace(item()?['header'],'5440','KZbin'),'5441','Google'),'5442','Facebook')

[@surajdevasthali6107]

Hi getting below error while creating the same type output with different data. Unable to process template language expressions in action 'ComposeHTMLTablesArrey' inputs at line '0' and column '0': 'The template function 'Output' is not defined or not valid.'. Please help

[@liamlindley9656]

@DamoBird365 Hi there - thanks a million for this video, I'm 95% of the way there but please can you help with the remaining 5%?! I wanted to add dynamic content to the final email, i.e. "Hi xxx" and signing off with "Kind Regards zzz" as that information is contained within my table array. I have 37 products supplied by 7 different suppliers. When i add dynamic content in the final stage of the video, the flow automatically generates an "apply to each 3", which then triggers 259 emails (37 x 7). Any ideas on how i can resolve this? Apologies - i've never been a coder!

[@DamoBird365]

I guess you have an unnecessary apply to each. It’s hard to comment without seeing your flow. Can you share it on the forum and tag me? Here’s a similar solution from the other day powerusers.microsoft.com/t5/Building-Flows/Import-Excel-rows-compose-table-and-send-email/td-p/1550093

[@AlzD3mi4n]

Hey Damien, Thanks a lot for sharing this content! I'm struggling to add the HTML Table in the object, the flow checker notifies me that something is wrong and that the JSON is not valid :( { "Hiring Manager (Manual)": @{first(body('Filter_array_Manager_names'))}, "HM Email": @{first(body('Filter_array_Manager_names'))?['HM Email']} "Html Table":"@{body('HTML_Table_Managers')}" }

[@DamoBird365]

Is it the quotes around the last value/expression. You have to escape @ in a string if it was required, hence the error.

[@AlzD3mi4n]

@@DamoBird365 When I add "Html table": "Output" (from the Html table created) the flow checker sends me a notification :S I can't add the image, but that's what happens, I'm not using an expression for this item.

[@satheeshkumar2670]

Thanks for fantastic tutorial, I have applied the same steps to my data and it works fine, but multiple emails are trigged based on the row. My data has 20 rows each manager has 10 rows when I run each manager getting their 10 rows data in HTML format whereas 10 email each person receiving repeatedly. Please guide me.

[@DamoBird365]

You must have introduced an apply to each in order for that to happen. I would suggest you share your flow on the Microsoft forum if it is not clear from my video.

[@kiransunka1]

Sir i need help. In my organisation i used to send alters and liat of items like data into table format for distinct client id. Kindly help. I have excel and O365 mail applications.

[@DamoBird365]

please try asking on the forum powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@bushy1892]

Loving the video, but having the same problem as Carla Grace Besa. This is at Compose action within Apply to Each(around 10:04 mark). I have followed the video and checked my input, but still getting Enter Valid Json. Pretty new to this, so probably missing something very obvious.

[@DamoBird365]

Can you share your json in the comments?

[@bushy1892]

{ "inputs": "{ \"Manager\";@{first(body('Filter_array'))?['Manager']} \"Html Table\";\"@{items('Apply_to_each')}@{body('Create_HTML_table')}\" }" } Many thanks for your assistance

[@bushy1892]

Working now. No idea why, but needed a colon instead of a semi colon. My first Power Automate flow and I'm really pleased with it. Perfect for the task I need it for. Thanks DamoBird365. Your video was really clear and easy to follow.

[@DamoBird365]

@@bushy1892 amazing 👍 keep up the learning. If there are any other scenarios that you struggle with, drop me a message and I may be able to do a video.

[@jagen2212]

Greeting Damien. I am having difficulties in the 1st apply to each after Create HTML table step. Once I have inserted the expression { first(body('filter_array'))?[' '] }. The output is not showing the Mutiple item as in your video. Can you help me out?

[@DamoBird365]

Look back at the history. Does the filter array have data? Can you see the field you’re looking for? Check the spelling/format of the field.

[@jagen2212]

@@DamoBird365 If I would like to put the date as a distinct value instead of client name. May I know how do I work around it?

[@jagen2212]

Original MANAGER CLIENT NAME Jason Mike 2nd Avenue Mike Smith 5 Golden Starts Mike Smith 98 Street Mary Doe 120 Welch Park MyTask CLIENT NAME Date 2nd Avenue 9/5/2023 120 Welch Park 9/5/2023 5 Golden Starts 9/6/2023 98 Street 9/6/2023 5 Golden Starts 9/7/2023 I would like to peform MyTask. kindly please advise hwo to work around

[@DamoBird365]

Hi, I think it would be better explaining this on a forum with photos, as it is not clear to me what you are trying to achieve powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@eileenohare-2055]

Best explanation I've watched so far. Nothing else I tried got me as close to what I need and trust me I've scrolled many! I'm a total novice so maybe you could tell me what I've done wrong. As a tester, i have 3 IDs across 4 rows of products so I should have 3 tables, one with 2 rows and the others a row each. That gives 3 tables or 3 emails. I'm getting more. It's duplicating the table email per row. So ID in row 1 gets 4 emails, each with the correct tables but not just theirs. Any help greatly appreciated 🙏

[@DamoBird365]

Hi 👋 Eileen. The apply to each should contain a filter, the html table should be based on this filter. The send email should be in this apply to each. Does your apply to each run 3 times - check the flow history? It should be based on your union to get distinct ids. Hope that all makes sense and 🤞

[@eileenohare-2055]

@@DamoBird365 It does, it looks ok to the select html tables but then again, I did say I'm a novice. But there are 4 runs in the final apply to each if thats what you mean. I tweaked it to try resolve the problem so I think the best thing to do now is repeat from scratch. Third time is a charm! Thanks for the very quick response.

[@DamoBird365]

@@eileenohare-2055 if you haven't already found it, the forum can be very handy powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums. I am on there when I am not so busy but it's supported by a lot of very keen folk who will definitely help you out. Let me know how you get on.

[@eileenohare-2055]

@@DamoBird365 I'll definitely check it out. I'll let you know when I get there for sure, I'm like a dog with a bone...

[@eileenohare-2055]

@@DamoBird365 Redid it and worked perfectly until I added dynamics to the email subject. So I see now that the looping is creating multiple emails. Is there any way of adding steps into your flow that will allow for a couple of these variables?

[@Lung-Ton]

How to set format number in html table form 1000.00 to 1,000.00 ? Thanks for helps.

[@DamoBird365]

You can use formatnumber learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#formatNumber or the formatbyexamples feature to build an expression for you.

[@Lung-Ton]

@@DamoBird365 Why table style not show on gmail but can display on outlook ?

[@tyopapsgaming3471]

Thank you so much for this, I am new in Power Automate. I just thought of this scenario, what if Manager Column "A" has two different Client ID Manager Client ID Manager 1 Client 1 Manager 1 Client 2 how can I make to send two separate emails to the same recepient "Manager"? thanks

[@DamoBird365]

Get the distinct clients using union. Then loop through the clients.

[@DamoBird365]

This also might help Send Unique Emails with HTML Table using Data from Excel #PowerAutomate kzbin.info/www/bejne/a57WqZtrit1rers

[@tyopapsgaming3471]

@@DamoBird365 Thank you so much, this helps a lot. One last question, while the above scenario works perfectly. I tried using your flow on a large data, 4000+ rows, then when filtering Manager, the unique Manager Name is 255, however upon Running the Flow it only sends 55 unique emails out of 255 unique manager. hope you can help me on this one.

[@DamoBird365]

@@tyopapsgaming3471 possibly pagination. Try using length() on value returned by get items. Maybe you’re not returning all rows.

[@tyopapsgaming3471]

@@DamoBird365 thank you once again for providing a solution to my query. Just to provide update, instead of using Length function. I turned on the Pagination option in "List rows present in a table" action and set it to 5000. Then i removed the Send an email action to test, and got a 255 Managers in the result.

[@abbieseale9417]

Thanks for this! Is there a way to have these tables sent as a .pdf attachment?

[@DamoBird365]

You could do that, definitely. You need to choose how you create the pdf. The word connector is premium but I share a free method in one of my videos. You could also use excel office scripts, again I have a video on that too.

[@abbieseale9417]

@@DamoBird365 Thank you!

[@abbieseale9417]

One more question (I’m new to Power Automate!) I’ve gotten the tables converted to .pdfs and attached to the emails, but there are some issues for Gmail addresses such as missing content from cells. Any tips for maintaining formatting when dealing with Gmail? Thanks again!!

[@DamoBird365]

@@abbieseale9417 I’m not sure I’ve seen this before. Maybe one for the forum? powerusers.microsoft.com

[@brendandoyle933]

One other thing, I want to filter and only send rows to Managers fitting another column criteria, for example Payment column = Due. I'm getting that if one email has entries with criteria DUE and Not Due, it will send an empty HTML table instead of sending only the DUE entries as I try to filter.

[@DamoBird365]

Hi Brendan, when you list rows, under advanced settings you can perform a filter on the column you mention. This will ensure that you only return rows with Due set. I assume that you are using filter array in your apply to each and it would therefore be possible that after this filter, no rows are present. The only way to get around that design would be to implement a condition where the length of the filter is greater than 0. But I would try filtering on the list rows action first as it would be more efficient👍

[@brendandoyle933]

@@DamoBird365 Ah that makes sense. I'm not overly familiar with the language used, what expression would I use to filter a column for 'DUE'? Thank you!

[@DamoBird365]

columnname eq 'DUE' 👍remember that it will be case sensitive and I don't think you can filter on columns with a space in the name.

[@brendandoyle933]

@@DamoBird365 Thank you!🍻

[@brendandoyle933]

One more question, is it possible to add a 'Total' row, summing the amount column? Thanks

[@ptyt1570]

oh yeah!!! regarding the client ID (first column), Do you have a magic trick to just have ONE Client ID either in the first row or last row instead of per row? Thank you!

[@DamoBird365]

You could do this by inserting a select and using range. Range is based on the length of the filter array. If the number item() equals 0, include the client id, else insert an empty string. Then supply the select to the create html table action in automatic mode (as you will need to call the other key.values by integer in the select using item() too. Like so: damobird365.com/images/display%20client%20ID%20once.png expressions are: ClientID: if(equals(item(),0),body('Filter_array')?[item()]?['Client ID'],'') Vendor etc: body('Filter_array')?[item()]?['Vendor Name']

[@ptyt1570]

@@DamoBird365 Thank you so much!

[@ptyt1570]

@@DamoBird365 lol, Ok, I'm dumb, I tried the whole afternoon but couldn't get it to work. I feel you gave me the answer but I don't get it. So, is your range expression: range(length(body('Filter_array')))....sorry for asking, thank you!

[@ptyt1570]

@@DamoBird365 lol, Ok, finally I think I got it. so the expression is range(0,length(body('Filter_Array')))....thank you very much!

[@DamoBird365]

That’s the one. How’s it looking? Bet you’ve learned loads? 👍

[@williamkerridge9765]

Great Tutorial but i am getting the following error at the compose stage after the apply to each, Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template function 'Output' is not defined or not valid.'.

[@DamoBird365]

Should it be outputs ?

[@nithyaa.n7474]

If I want to filter only one column data and how to send it through multiple Manger

[@DamoBird365]

Let me know if the following video helps you - it's similar kzbin.info/www/bejne/a57WqZtrit1rers

[@nithyaa.n7474]

Thank you for your response but I want same thing what you did in this vedio I want something but only I want take client name and send it to appropriate users

[@DamoBird365]

Ok, I think you want to limit the columns in the html table? There is an advanced options where you can define the columns using expressions/dynamic values.

[@dineshrawat4327]

Hi sir, I had done what you showed in video, but need is little different, I had to mail different clients I'd wise . Pls help

[@DamoBird365]

My recommendation is to ask on the forum powerusers.microsoft.com/

[@dineshrawat4327]

@@DamoBird365 in same video , done , but send client id wise different mail to same maanger

[@dineshrawat4327]

What add in flow

[@karlagureys]

I have a problem in 10:04, it says, "enter a valid json" what goes wrong after following your exact instruction?

[@DamoBird365]

Which action would a giving that error? Most likely your missing a squiggly bracket or inverted comma in something.

[@bushy1892]

Carla Grace Besa - did you manage to resolve this issue? I added my Json to a couple of Json checkers and they have all confirmed it is correct. I've recreated my flow to this point several times and tested it. It works fine until I have to add Json. I'm at a complete loss what to do now. Video is fantastic - just wish I could get past this Json error.

[@gennarom8484]

Hello Damien! Getting crazy how to create a condition such as: if in a cell there is NOT 'Client Name' but next cell contains data, THEN the WHOLE row will not appear in the email. Example: Client Id = 123 Client Name [empty cell] Vendor Name = Smith ///So in this case row will not be included in the email. Thanks for helping me, I am getting crazy! :)

[@DamoBird365]

Hey Gennaro, I’m sure there will be a way. You should be able to use a filter array action, where the client name item()?[‘client name’] is equal to null (note there is a null expression). Then create an html table on your filtered results. Let me know if that helps.

[@gennarom8484]

​@@DamoBird365 Thanks, I guess I should put a Filter Array before the first "Apply to each" and after ComposeDistinctClientNames, right? In the "from" I put "value Excel icon" and below your expression item()?[‘client name’] is equal to ' null ' ? Is that ok? What is the exact espression for null? Can't find any reference for that :(

[@chrishofmann5965]

Can you use gmail to send the email?

[@DamoBird365]

It does look like it is possible via the Gmail Connector learn.microsoft.com/en-gb/connectors/gmail/#send-email-(v2)

[@mariuszzbikowski7122]

Ideally please add in description the expression to replace problematic tags

[@DamoBird365]

Hi Mariusz, can you explain to me what you mean with an example? Thanks.

[@epicrawat6053]

Sending mail is ok sir, but mail sent that times the number of rows present in the table. , Thanks in advance, pls help to out it

[@DamoBird365]

I would guess you’ve added an unexpected apply to each. I would recommend you watch kzbin.info/www/bejne/mmqcg6Vrmc-Wg6c

[@ceciliaaugustine3669]

Hi Damo, I am facing a bit of a challenge, I have multiple emails sending instead of one for each manager. How do I fix this?

[@DamoBird365]

I’m guessing you’ve created an additional apply to each loop somehow? Maybe remove that and revisit that stage of the demo?

[@ceciliaaugustine3669]

@@DamoBird365 I followed your demo to the t! is it at all possible to show you via a google meet maybe you can assist?

[@DamoBird365]

Can you drop me an email and I’ll try [e-mail]

[@ceciliaaugustine3669]

@@DamoBird365 i found the error! One apply to each I had created by mistake just above the send email. Once done, I was able to do my task with a click of a button! Thank you so much

[@DamoBird365]

@@ceciliaaugustine3669 glad you cracked it 🙂

[@m.s.1659]

Hi, for some reason I cannot make the flow work. I tried many times, copying exactly every single step as you do, yet I do not have the same result as you have...

[@DamoBird365]

Hard to tell you what’s wrong on here without info. Have you shared your flow on a forum? powerusers.microsoft.com/

[@m.s.1659]

@@DamoBird365 Thank you so much for your reply and consideration, the error message is in CREATE HTML TABLE under APPLY TO EACH: InvaldTemplate (Unable to process template language expressions in action 'Create_HTML_Table' inputs at line '0' and column '0': 'The template language expression 'outputs('Filter_array')['body/value'] cannot be evaluated because property 'body/value' doesn't exist, available properties are 'body'. In fact in your presentation, you had 'body' but I see only selection available 'body/value'. I will follow the link to FORUM, hopefully someone else has this issue.

[@DamoBird365]

@m.s.1659 are you using the new designer? If so, select body/value from the classic.

[@m.s.1659]

@@DamoBird365 Hi, yes probably I have new designer, and I did select always 'body/value', there was no other option. But that is indeed what is creating the issue, The message is : property 'body/value' doesn't exist.

[@m.s.1659]

I managed to complete it - and I have the same results as you have 🙂 I took another class to understand the basics, and now I know where the switch to NEW DESIGNER is. The only issue is that DATE is showing in HTML Table in e-mail as number value, which is 45107, 45115, 45116 etc rather than 30/06/2023, 08/07/2023. Do you have any suggestion how to fix it? Many thanks again 🙂🙂🙂

[@joshuakelly7231]

My issue is that the filter array brings back empty values and just spits out the HTML formats and not the values. :(

[@DamoBird365]

Double check your filter array. Hopefully you’ve got a typo in there. Let me know how you get on.

[@joshuakelly7231]

@@DamoBird365 I'm connecting to a SharePoint list not an Excel file. I'm sourcing the filter variables from the SharePoint List settings. The input in the array is working nicely, it's the output. Like you mentioned, it's likely the filter array but I'm not sure what else I can do since I know the variable nomenclature being used is correct. Are some of the syntax we're using not laterally equal for these different endpoints? .

[@DamoBird365]

@@joshuakelly7231 SharePoint columns have an internal name. You can check it in the address bar when you select the column from list settings. Or look at the history output of list rows. You’ll see the json.

[@joshuakelly7231]

@@DamoBird365 Yeah. That's what I was doing. After a few more hours of staring, I might be able to find what I'm doing wrong. I've been using a JSON parser to ensure I"m exact. It's probably something silly I'm missing. Typically is! haha. Appreciate the response! Also, nice guide! Thank you!

[@aarunishekharjha5482]

Thanks Damien!! What would you do if you needed to extract the data from the body of an email and create an excel file from it?? Let's say the body of the email is: Category: Electronics Products: Television, Laptop, Mouse, Earphones Price: $500, $400, $50, $40 The objective is to have different products and prices in different rows for the category electronics. And every time i receive an email the excel file should get updated automatically with different categories. Could you please help me out?

[@DamoBird365]

If it's an HTML table in the email, check this out kzbin.info/www/bejne/n2WqeXZ9gqZ3bpo

[@DamoBird365]

And then once you have an array, create a new Excel file kzbin.info/www/bejne/iHPCqoagndJsick

[@aarunishekharjha5482]

@@DamoBird365 It is not an HTML table in excel. Also to add on the products and price should be in different rows for the category.

[@DamoBird365]

@@aarunishekharjha5482 I don’t think you’ve understood. Is it an html table in the email? You can create a json array from that data via the first video. Then you can create an excel file from a json array in the 2nd video. Or is the email plain text and 3 lines? In which case you can create an array with split() and select. Maybe one to discuss on the MS forum?

[@aarunishekharjha5482]

@@DamoBird365 The email is plain text and three lines. Thank you for clarifying.

[@neipots1182]

testing my flows takes too long

[@lpdowdell]

Bloody long session! Come on - make these videos a lot shorter.

[@DamoBird365]

Hi Linda, please can you explain further, I am always looking for feedback. Are you looking to solve a specific use case? Did I go into too much detail or do you have a different use case?

[@lpdowdell]

@@DamoBird365 too much into details - pls feel free to email me so that I can provide feedback.

[@DamoBird365]

Drop me an email [e-mail]

[@osamaaljaafari.6645]

This was a great tutorial. Please can you explain to me how to send an excel sheet instead of the HTML table with the same procedure, thank you for your always help.

[@DamoBird365]

I’ve got the idea on my to do. I will eventually put something together I hope 🤞

[@osamaaljaafari.6645]

@@DamoBird365 eagerly await