I’ve got a favour to ask! If you enjoy this, please double check that you’ve liked the video and subscribed to the channel! Thats a small way you can help me carry on doing this ❤🙏🏾 really appreciate you! 🩷 Subscribe: th-cam.com/users/DamoBird365 ☕ BuyMeACoffee: buymeacoffee.com/DamoBird365 🔗 LinkedIn: linkedin.com/in/DamoBird365 🐦 X (Twitter): x.com/DamoBird365 📱 TikTok: tiktok.com/@DamoBird365 🙋♂ Feedback: forms.office.com/r/4EqE7VHVfH
absolutely amazing, now all i have to do it work out how to convert that email flow into a meeting invite and start and end dates from the excel sheet! thanks for this, really well explained too!
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. 👍
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! :)
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.
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
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.
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👍
@@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!
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.
My latest video should help you Power Automate | Apply to Each | Why? | Arrays and Objects th-cam.com/video/d99Rr5djcME/w-d-xo.html. A select action and a join on the people picker results.
Hey, thanks for the video, I'm not sure where I am going wrong but I have this piece of code {"Status":"Valid","HTML Table":" appearing in the email, and for some reason my tables are going to the same person as separate emails, and despite using the hoover code from the W3school link, it's not working. any idea how I fix this? I have to omit some elements of your video because I only want 2 tables filtered by 1 column heading and they are being sent to the same person. Not sure if this is where I have gone wrong.
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?
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
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
Are you looking to create separate excel files and email them? The trigger, when file updated, might cause unnecessary emails. Maybe a manual trigger is better.
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.
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.
@@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.
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?
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 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.
@@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!
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;}
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?
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
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)
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’
You could use a repeating control which is premium, yes. th-cam.com/video/lBXSsEIlWok/w-d-xo.html or check out th-cam.com/video/L67m8wgd4Ak/w-d-xo.html for various document options
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
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?
@@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
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/
Not tried this yet but love what you done with office scripts, can we do this without the apply to each? I want to try this from getting data from power bi and then put it into this html format and send email
The apply to each is needed for the sending of each email, as each email is unique. This is another similar video th-cam.com/video/5msxh5Ux8DY/w-d-xo.htmlsi=HjXw6kP0ZwxZQg4L
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.
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 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 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.
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.
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
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 th-cam.com/video/PD980sKKx0E/w-d-xo.html and th-cam.com/video/5msxh5Ux8DY/w-d-xo.html
@@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 I would like to have separate tables, just like you did. But I realize that using the flow having different managers only takes the first.
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.
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.
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.
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.
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)
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.
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
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.
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.
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.
@@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?
@@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 th-cam.com/video/hW47n-StNUc/w-d-xo.html
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.
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!
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.
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!!!
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.
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 just tried and that worked perfectly! re-worked slightly for my use case but incredibly helpful! appreciate your video and for responding!
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.
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 👍
@@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.
@@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 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 :(
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?
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
Your video means a lot for me ❤ The only thing I need to know is how to include the client's name in the email subject, especially if the email contains multiple client names.
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
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.
P.s. I tend to construct expressions manually JSON Array in Power Automate - Walkthrough lesson with examples #PowerAutomate #JSON #Array th-cam.com/video/6nJSUNh579w/w-d-xo.html
@@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
@@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!
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?
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.
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.
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.
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😅
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.
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?
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.
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
So followed this and worked perfect thanks for another great video. Quick question How can we add a subtotal/total for each clientid on the tax amount? Under each table In thia video it would be great to have a subtotal for each table. In your example welch park would have a summed amount at the bottom and so on for all the others. I tried your aggregation video using xpath and can get the totals but cnt get them together. Is this possible?
Cheers Lee. Yes, you would have to combine both. The easiest way to add the total is to use xml, create an array with single object, same shape as your data and then use union() to bring the full array together with the total array of 1 object.
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?
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!
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.'.
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?
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.
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)
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
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
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
@@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?
@@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!
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!
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.
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?
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 .
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? th-cam.com/video/CZFLjy8hEx0/w-d-xo.html
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 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 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.
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 🙂🙂🙂
: not ; worked for me too. ChatGPT checked it for me and confirmed and then explained why. But strangely the ; works in the video and when I zoom in it looks like a ;. Regardless great video and instruction.
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 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 :-)
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 🙏
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 🤞
@@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.
@@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.
@@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?
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 'TH-cam' replace all '5441' in the table with 'Google' replace all '5442' in the table with 'Facebook' thank you
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.
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.
@@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
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.
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 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!
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
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 ?
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 damien@damobird365.com if you want to discuss.
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'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? .
@@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.
@@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!
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!
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']
@@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!
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?
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! :)
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.
@@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 :(
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 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.
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?
@@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?
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?
I’ve got a favour to ask! If you enjoy this, please double check that you’ve liked the video and subscribed to the channel! Thats a small way you can help me carry on doing this ❤🙏🏾 really appreciate you!
🩷 Subscribe: th-cam.com/users/DamoBird365
☕ BuyMeACoffee: buymeacoffee.com/DamoBird365
🔗 LinkedIn: linkedin.com/in/DamoBird365
🐦 X (Twitter): x.com/DamoBird365
📱 TikTok: tiktok.com/@DamoBird365
🙋♂ Feedback: forms.office.com/r/4EqE7VHVfH
absolutely amazing, now all i have to do it work out how to convert that email flow into a meeting invite and start and end dates from the excel sheet! thanks for this, really well explained too!
Once you understand how to use these actions to their fullest you can do so much it actually becomes scary. Thanks for this, mate.
😂 absolutely. Very powerful, I love it. I love hearing of automations too!
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. 👍
Oh wow thank you.
This is what I've been looking for. You are the best.
Thank you so much
You're the best!!!! Thank you so much
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! :)
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.
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
This video is a Hand Book Chapter for me. Thanks
Thank you. If you think of any other scenarios I could cover, please do drop me a dm.
Brilliant as usual! Thx so much for your work!
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.
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👍
@@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!
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.
@@DamoBird365 Thank you!🍻
One more question, is it possible to add a 'Total' row, summing the amount column? Thanks
one of the createst descriptions, but what if i have just the manager name in this excel file and the manager name + email in a different table?
You could look up the email 👍
Great video! I'm going to try this on an Excel Order form.
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.
My latest video should help you Power Automate | Apply to Each | Why? | Arrays and Objects
th-cam.com/video/d99Rr5djcME/w-d-xo.html. A select action and a join on the people picker results.
Hey, thanks for the video, I'm not sure where I am going wrong but I have this piece of code {"Status":"Valid","HTML Table":" appearing in the email, and for some reason my tables are going to the same person as separate emails, and despite using the hoover code from the W3school link, it's not working. any idea how I fix this? I have to omit some elements of your video because I only want 2 tables filtered by 1 column heading and they are being sent to the same person. Not sure if this is where I have gone wrong.
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?
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.
@@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?
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
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
can i attach tables from excel sharepoint in email body and automatically send with triggers (anytime it is edited)???
Are you looking to create separate excel files and email them? The trigger, when file updated, might cause unnecessary emails. Maybe a manual trigger is better.
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.
Can you share your json in the comments?
{
"inputs": "{
\"Manager\";@{first(body('Filter_array'))?['Manager']}
\"Html Table\";\"@{items('Apply_to_each')}@{body('Create_HTML_table')}\"
}"
}
Many thanks for your assistance
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.
@@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.
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?
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.
@@DamoBird365 Thank you for the quick response. Yes there are values for Focalpoint in the output history.
@@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.
@@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!
Very strange but glad you’ve got it working 👍
just the one i need right now. thanks for the video.
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;}
@@badriruslan8797 www.w3schools.com/html/tryit.asp?filename=tryhtml_table_hover
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?
I’m not sure to be honest. Maybe try the official forum?
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
Thanks for this great video! Do you possibly know why I dont see 'value' option in list rows present in a table? Only body and body/value
Maybe the new vs classic designer?
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)
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’
Is it possible to use the dynamic table in a word document?
You could use a repeating control which is premium, yes. th-cam.com/video/lBXSsEIlWok/w-d-xo.html or check out th-cam.com/video/L67m8wgd4Ak/w-d-xo.html for various document options
HI @DamoBird365 want to generate Pie chart from Power Apps or SharePoint. How can we do that? Instead of Excel SharePoint can we use?
check out quick charts api as an option maybe?
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
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?
@@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
Hi Damo - is it possible to replace the send mail action here with the process shown in your video on creating a draft?
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/
Not tried this yet but love what you done with office scripts, can we do this without the apply to each? I want to try this from getting data from power bi and then put it into this html format and send email
The apply to each is needed for the sending of each email, as each email is unique. This is another similar video th-cam.com/video/5msxh5Ux8DY/w-d-xo.htmlsi=HjXw6kP0ZwxZQg4L
I have a problem in 10:04, it says, "enter a valid json" what goes wrong after following your exact instruction?
Which action would a giving that error? Most likely your missing a squiggly bracket or inverted comma in something.
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.
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
Get the distinct clients using union. Then loop through the clients.
This also might help Send Unique Emails with HTML Table using Data from Excel #PowerAutomate
th-cam.com/video/5msxh5Ux8DY/w-d-xo.html
@@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.
@@tyopapsgaming3471 possibly pagination. Try using length() on value returned by get items. Maybe you’re not returning all rows.
@@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.
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.
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
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
th-cam.com/video/PD980sKKx0E/w-d-xo.html and
th-cam.com/video/5msxh5Ux8DY/w-d-xo.html
@@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
@@svenm.3881 I think you’ll be best on the forum for this as folk can then see your logic powerusers.microsoft.com/
Thank you for your video . And how do I do if I have the same client with several managers ?
Depends on what you want to do as an output? What’s your requirements?
@@DamoBird365 I would like to have separate tables, just like you did.
But I realize that using the flow having different managers only takes the first.
outstanding 👏
@@blessedtheotokos3899 thank you thank you 🙏
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.
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.
Thanks for updating me 👍 well done on working it out and enjoy the learning.
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.
please try asking on the forum powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
How to set format number in html table form 1000.00 to 1,000.00 ? Thanks for helps.
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.
@@DamoBird365 Why table style not show on gmail but can display on outlook ?
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.
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)
Here’s a recent example from another Super User ABM th-cam.com/video/5PG8bjUXgig/w-d-xo.html
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.
I’ve a video on dates. Maybe look at parsedatetime(). th-cam.com/video/ybPWtu1i1j0/w-d-xo.html
Hi, i am not getting Dynamic content option, i am trying to send table from power bi itself using power automate inbuilt
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?
If I want to filter only one column data and how to send it through multiple Manger
Let me know if the following video helps you - it's similar th-cam.com/video/5msxh5Ux8DY/w-d-xo.html
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
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.
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.
any suggestions/guidance on this would be great!! :)
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.
@@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?
@@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
th-cam.com/video/hW47n-StNUc/w-d-xo.html
@@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
Top notched contents. Thank you! Subscribed.
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.
It’s tried and tested, Pieter Veenstra sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows just promise to come back 👍😂
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!
Hopefully it was this one 👍 Send Unique Emails with HTML Table using Data from Excel #PowerAutomate
th-cam.com/video/5msxh5Ux8DY/w-d-xo.html
@@DamoBird365 Thanks! I was watching that video but I didn't finish it, so I missed the solution I was looking for.
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.
You could build an advanced html table using a select and this would not include a header. th-cam.com/video/LQumCR1B-q0/w-d-xo.htmlsi=gRev3dKUYihdxZXJ
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!!!
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 🤞
tks bro!!
@@DamoBird365
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.
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?
This might help? Send Unique Emails with HTML Table using Data from Excel #PowerAutomate
th-cam.com/video/5msxh5Ux8DY/w-d-xo.html
@@DamoBird365 just tried and that worked perfectly! re-worked slightly for my use case but incredibly helpful! appreciate your video and for responding!
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.
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 👍
@@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.
@@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...
It should work with any data source. I guess you must have create distinct clients, instead of manager?
@@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 :(
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
My recommendation is to ask on the forum powerusers.microsoft.com/
@@DamoBird365 in same video , done , but send client id wise different mail to same maanger
What add in flow
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?
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
Your video means a lot for me ❤
The only thing I need to know is how to include the client's name in the email subject, especially if the email contains multiple client names.
helped me do exactly what I needed!
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
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.
P.s. I tend to construct expressions manually JSON Array in Power Automate - Walkthrough lesson with examples #PowerAutomate #JSON #Array
th-cam.com/video/6nJSUNh579w/w-d-xo.html
@@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
@@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!
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?
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.
@@DamoBird365 i am using this expression: first(body('Filter_array_2'))?['Email - Kortansvarlig']
Check your filter array. It must have returned 0 results. You can see this in the flow history.
If I, in the "To"-field, choose to send it to myself everything looks like it should.
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.
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.
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😅
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.
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?
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.
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
Try out formatNumber(123,'£0.00')
So followed this and worked perfect thanks for another great video. Quick question How can we add a subtotal/total for each clientid on the tax amount? Under each table
In thia video it would be great to have a subtotal for each table. In your example welch park would have a summed amount at the bottom and so on for all the others. I tried your aggregation video using xpath and can get the totals but cnt get them together. Is this possible?
Cheers Lee. Yes, you would have to combine both. The easiest way to add the total is to use xml, create an array with single object, same shape as your data and then use union() to bring the full array together with the total array of 1 object.
Thank you Sir! Your response is super fast and have helped what felt like the impossible.
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?
You can explore ideas on w3schools www.w3schools.com/css/css_text.asp
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!
It is possible and I demo it in another video I think. Your file name could be dynamically constructed using formatdatetime.
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.'.
Should it be outputs ?
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?
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.
docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#contains
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)
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
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.
@@DamoBird365 I will try, thank you very much
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
I’m not sure I follow you. Can you share an example?
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
@@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?
@@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!
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!
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.
@@DamoBird365 Thank you! This worked perfectly.
Thanks Hopefully this will work in my case
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?
Under advance there is date format? Select iso8601 instead of serial.
@@DamoBird365 That worked, thanks very much.
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 .
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? th-cam.com/video/CZFLjy8hEx0/w-d-xo.html
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...
Hard to tell you what’s wrong on here without info. Have you shared your flow on a forum? powerusers.microsoft.com/
@@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.
@m.s.1659 are you using the new designer? If so, select body/value from the classic.
@@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.
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 🙂🙂🙂
Thanks for the video. Unless I misheard, I think you may have confused the semi colon (;) and colon (:). Example at 7:33
: not ; worked for me too. ChatGPT checked it for me and confirmed and then explained why. But strangely the ; works in the video and when I zoom in it looks like a ;. Regardless great video and instruction.
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!
Try format number? docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#formatnumber
@@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 :-)
@@55SabreDog it would be formatnumber(item()?[],etc)
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 🙏
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 🤞
@@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.
@@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.
@@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...
@@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?
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 'TH-cam'
replace all '5441' in the table with 'Google'
replace all '5442' in the table with 'Facebook'
thank you
just put replace(item()?['header'],'5440','TH-cam') in the convert to HTML Table step but for mulitcondition how to done
replace(replace(replace(item()?['header'],'5440','TH-cam'),'5441','Google'),'5442','Facebook')
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.
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.
This is super awesome. I have a question. What is the date format on my excel is dd-mm-yyyy hh:mm:ss tt? How does the HTML table works?
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?
I’m guessing you’ve created an additional apply to each loop somehow? Maybe remove that and revisit that stage of the demo?
@@DamoBird365 I followed your demo to the t! is it at all possible to show you via a google meet maybe you can assist?
Can you drop me an email and I’ll try damien@damobird365.com
@@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
@@ceciliaaugustine3669 glad you cracked it 🙂
Thanks for this!
Is there a way to have these tables sent as a .pdf attachment?
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.
@@DamoBird365 Thank you!
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!!
@@abbieseale9417 I’m not sure I’ve seen this before. Maybe one for the forum? powerusers.microsoft.com
@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!
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
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 ?
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 damien@damobird365.com if you want to discuss.
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.
I’ve got the idea on my to do. I will eventually put something together I hope 🤞
@@DamoBird365 eagerly await
My issue is that the filter array brings back empty values and just spits out the HTML formats and not the values. :(
Double check your filter array. Hopefully you’ve got a typo in there. Let me know how you get on.
@@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?
.
@@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.
@@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!
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!
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']
@@DamoBird365 Thank you so much!
@@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!
@@DamoBird365 lol, Ok, finally I think I got it. so the expression is range(0,length(body('Filter_Array')))....thank you very much!
That’s the one. How’s it looking? Bet you’ve learned loads? 👍
Can you use gmail to send the email?
It does look like it is possible via the Gmail Connector learn.microsoft.com/en-gb/connectors/gmail/#send-email-(v2)
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?
If you’ve got an array, it’s just the same idea as the excel list rows returns data as an array.
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
I would guess you’ve added an unexpected apply to each. I would recommend you watch th-cam.com/video/d99Rr5djcME/w-d-xo.html
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! :)
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.
@@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 :(
Ideally please add in description the expression to replace problematic tags
Hi Mariusz, can you explain to me what you mean with an example? Thanks.
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')}"
}
Is it the quotes around the last value/expression. You have to escape @ in a string if it was required, hence the error.
@@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.
It’s better to use a sharepoint library rather than a one drive folder , for stability and reliability
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?
If it's an HTML table in the email, check this out th-cam.com/video/i4GHCGMAD88/w-d-xo.html
And then once you have an array, create a new Excel file th-cam.com/video/RB_ySjhm9Sg/w-d-xo.html
@@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.
@@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?
@@DamoBird365 The email is plain text and three lines. Thank you for clarifying.
testing my flows takes too long
Bloody long session! Come on - make these videos a lot shorter.
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?
@@DamoBird365 too much into details - pls feel free to email me so that I can provide feedback.
Drop me an email ideas@damobird365.com