Hi Damien, as with our email discussion. Some of you guys might face some issues with the xpath when following this tutorial. You might get this error when you run the flow that is shown in this tutorial : "Jason property name: the property with name " already exists.". Fear not, this is an issue with xpath. In my working solution to select 'Country', I used the following xpath. " xpath(xml(outputs('ComposeHTML')), concat('//table//tr[1]/td[1]//text()'))?[0] (Use wildcard to select text, aka use "//text()" rather than "/text()", as the formatting for the table headers might not allow you to select the text() properly.) Do the same for the remaining column headers and anytime that these column headers are used in the tutorial. Another best practice for Microsoft flow is to create variables first, so it is easier to just change 1 variable declaration rather than finding all the points where this expression is used and changing it individually. I hope this helps!
@@DamoBird365 Hi Damo and Nikki, many thanks for your time and sharing all this knowledge. I have tried the above solution and still get stuck at a similar error. InvalidTemplate. The execution of template action 'SelectRowsFromHTML' failed: The evaluation of 'query' action 'where' expression '{ "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[',item(),']/td[1]//text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[',item(),']/td[2]//text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[3]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[',item(),']/td[3]//text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[4]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[',item(),']/td[4]//text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]//text()')?[0]}' as JSON property name: the property with name '' already exists.'
You probably want to check the history output of the compose above this (as it's the xml() expression that is failing) and check to see if your html is valid. Alternatively add in another compose, without the xml() expression and check the history output. There is presumably something in there that isn't valid, maybe you have additional tags or are missing tags.
Thanks! This was great. I was actually trying to extract an html table from an email in Azure Logic Apps, but couldn't find anything useful. Luckily I thought to search for Power Automate instructions and this came up. A few little tweeks in xpather and I got my app working!
Hey, great detailed video. Unfortunately, I keep getting an error "indexof' expects its first parameter to be of type string" The output from the email seems fine for HTML table. any ideas guys?
Check the expression in indexof. If you add it to a compose, you can check it back in flow history and ensure there is some data being output. If there is a typo, it will output null.
Excellent demo you have shown... . I am having little confusion what will be the solution, if the table has no but s inside 6th < TR> has all the header names. and from 7th has all the s with data ..
You could just get all of the via the select and xpath, and then use skip to drop the first 6 rows. docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#skip
Hi Damien, as I am new to Power Automate. I struggled to download your flow solutions and import into mine. Should i be using power automate desktop? And which file should be imported into power automate?
Great video, It pointed me in the right direction with my issue. How every I have one that has style - Critical Error - When I run the flow the appears as null. I have tested without the style and it works. But how do I grab the value without deleting the style?
Hi Damien, when I go to create "add a row into a table" under "Apply to Each", in the Row as per video i have to put "Item()" but I am only able to find Items('Apply_to_each') or Items('Apply_to_each_Vs_Select'). I need "Item()" but I can't put, please help thanks.
Hey Damien, Again I need your assistance with this error "A value must be provided for item. clientRequestId: 739205ec-8408-4b98-8cb8-eba8604d2712" in our 2nd Scope - apply to each - Add a row into the table I have been through the community support for the same named issue, not useful they were. by far till now it is generating excel file at location with Table inside, but emailed data is not available in table. Thanks again.
Is there anyway to get this flow with the Sharepoint portion removed - I cannot import this tool because I do not have Sharepoint permissions at my university, so it keeps me from importing.
Hey Damien, thank you for the video. I'm currently following through each step, but I seem to get stuck at the first compose inside the Scope-GetHTMLDataAsJSONArray. It doesn't seem to let me add an expression but only parameters. Is it because I'm using "When a new email arrives in a shared mailbox (V2)" as my initial trigger?
This is a great video, thank you. I do have 2 questions: 1. I am understanding the table syntax, but the header in my email body is in a column on the left side and the data I want to copy into excel file is in the column on the right side, is there a syntax or video you can share for me to learn? 2. can you confirm, since I already have a table I wouldn't need to create a excel table rather add a row and apply each field to match header in the table? thank you again.
Hey, needed a bit of clarity on something, so at 16:50 when the xpath expression is run, I tried following along on my own use case and found that the text function is not returning an output, xpather is showing an hourglass symbol. However, without the text function being explicitly mentioned, I am able to get the required and expected output. What exactly seems to be the problem here.
Hi Damien, i get the below error in the ComposeHTML step Unable to process template language expressions in action 'ComposeHTML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'Data at the root level is invalid. Line 1, position 1 Can you please help here
@@DamoBird365 Thank you for the response.. Working on the column part starts with the select query if i am not wrong..But i get the error two steps before that itself which is the ComposeHTML part..
This didnt work but it was the last straw that broke the camels back. I had to go and learn how to use Arrays, strings, split and int (all from your vids) and have managed to make something that works despite the CSS stopping this solution working for me :)
@@DamoBird365 I managed to get it working in test but as soon as I did a live example I found my company has added a message warning about emails from external users. This is contained within a table and messes up the flow. I am now trying to work out how to select the 2nd table.
Hi Damien, I have been Struggling with 2nd Compose, it pops up error "The expression is invalid" , the one have Substrings "substring(outputs('ComposeStartOfTable'),O,indexof(outputs('ComposeStartOfTable'),''))"
Really great video thank you very much! However I have some trouble because my HTML table received in the email contains inline CSS And I have no clue on how to extract only the table tags only Any idea ?
You might need to attempt a tidy up using replace before trying the xml functions? I’ve not had experience of this one so am not sure. Would be good if you could share your findings.
Damien great tutorial I have this working in a sense my HTML has hyperlinks in it and when export to excel is show null value. I have been trying to figure out to parse the text and not the hyperlinks in order to popluate the column have you ran accross this?
@@DamoBird365 This worked out awesome man I was going in like 10 different directions. I am not trying to figure out how to get the Owners of these applications and create a mail to in the html, but I am having issues it is not as easy as I originally thought. Any help would be awesome if you have tried to do this previously.
@@dollman2002 I don’t fully understand what you’re trying to do, the forum might be able to help you. Here’s w3schools for a mail to www.w3schools.com/html/tryit.asp?filename=tryhtml_mailto
@@DamoBird365 Yes I downald the file that was shared in github, but really im a new user in power automate and I do not have tu many idea about programation. What´s expected to do with the json files that I have downald?
@@germandeseta735 you should have downloaded a zip? Don’t unzip it. Go to make.powerautomate.com/ choose my flows, import, import package (legacy). Then follow the wizard and import as new 👍
I have created an excel file with table and columns in onedrive. I got Json array of my table data from email. My question is, how to add those array data to rows and remove duplicates afterwards.
Hey Santosh, probably one for the forum, however you can compare arrays efficiently using this method pnp.github.io/blog/post/comparing-two-arrays-without-an-apply-to-each/ those that match, remove, those that don’t, add.
Hi Damien, Can I use the SelectRowsFromHTML action if I receive tables with the same headers but different number of rows each time? From what I understand, it can only be used if the number of rows received is fixed.
First 4 steps run fine.. but at the ComposeHTML step I'm getting the following error:Unable to process template language expressions in action 'ComposeHTML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'Reference to undeclared entity 'nbsp'. Line 1, position 8201 Any easy fix to this fail point? Thank you
The character entity is used to represent a non-breaking space in HTML, but it's not a valid character entity in XML or XPath. If you encounter an error in Power Automate when using XPath expressions that contain , you can try replacing it using replace() before passing it to xml().
@@DamoBird365 Thank you... still new to PowerAutomate so I'm not sure I completely follow. I may send you an email tomorrow to inquire your services for assistance. Thank you.
Hi Damien - Thanks for this tutorial. I'm trying this process and I can't seem to get past ComposeHTML. I'm seeing the error -- "The 'col' start tag on "some position" does not match the end tag of 'colgroup'. In my HTML expression I have start tag for colgroup and four col elements (representing my columns) and an end tag for colgroup (no start and end tag for col, just directly col and its parameters for each column). But with this segment, I suppose it should have been addressed given the replacement of with . I wonder what I'm missing.
I FIGURED THIS OUT!!! I also have 4 columns, so in that 'replace' function, i literally just made sure there were 4 tags too. The coding looked like this () and I noticed that each column starts with
Do you have any idea of what can i change in step composehtml, because I´m trying to run the flow with an email that contains another html and sends me the message of error in that step. When I try with the html of the example, the issue does not happen. Thanks for the video
Hi. I've tried this, however my tables are formated with colors, styles etc and it seems to interfere with xpath. Got any hints on how to remove all the formats/styles? I was thinking to do find/replace composes, however I am not an expert in html and hard to know what I can delete without breaking it. :) Much appreciated
Hi there, another option you have got is ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-z-xQ2RoCerA.html. When trying via XPath, did you get any errors? It's hard to comment without examples but there are other approaches or a combination of both and definitely trial/error.
In desktop automate , one email have multiple data to crop. I am able to successfully crop (using flag between two text) only first set of data. I not able to crop the next ...set of text data in that same email. How to do that?
Hello Damien, great walk-through. The tools you demonstrated to validate and help generate your xpath syntax is one of those things that only collaboration with others can bring, thanks for sharing that. My use is should actually be simpler, as I just need to pull data from an email that has a table in the same format every time. I may actually shift to that static table idea if I can't figure out my problem soon. My table always has 2 rows, the first being the header, the second where the data I am after will be. There are 10 fields, I have 10 entries in the SelectRowsFromHTML action. My original issues were syntax, my numbering was off. Now everything looks aligned, but I get the following, any ideas? To be clear, I am reading 10 fields from the second row of a table. My intent is to place those fields, in order, on a single-line text file. Should be easy enough, but I can't get past this "already exists" error. InvalidTemplate. The execution of template action 'SelectRowsFromHTML' failed: The evaluation of 'query' action 'where' expression '{ "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[1]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[2]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[3]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[3]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[4]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[4]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[5]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[5]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[6]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[6]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[7]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[7]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[8]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[8]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[9]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[9]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[10]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[10]/text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}' as JSON property name: the property with name '' already exists.'.
@@DamoBird365 I have gotten further along. I ran into issues with and (carriage return and newline I believe) everywhere so I changed from xml to string again, did replace with nothing for both. and now the output is cleaner. Now it giving no start tag: [\ and span and even td, which is odd since the open tags are clearly there. I need the td since that is where my data is. I will try to remove the span and p and hopefully will work.
Just noticed that you are doing your replaces within the xml expression. I thought I had to convert to string first....and I dropped the xml...going back to that now.
this is video is great, also i needed complete an excel file already created in sharepoint,that every time the table is extracted only update that same file you can help?
Hello Damien, I wonder if you can help me with this error: InvalidTemplate. The execution of template action 'SelectRowsFromHTML' failed: The evaluation of 'query' action 'where' expression '{ "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[1]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[2]/text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}' as JSON property name: the property with name '' already exists.'. it happens on the SelectRowsFromHTML step and only after I try to insert the second column into the step. Thanks in advance.
Hi @Damobird365 , i really like your video.. but I have some text in the mail after one paragraph, I have 2 table in mail.. plz do one video on it . Please help
Hello Damian , As per the tutorial and comments shared by other users . I made the changes , but the workflow still gives the same error ."The execution of template action 'SelectRowsFromHTML' failed: The evaluation of 'query' action 'where' expression '{ "@variables('varCol1')": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[0]//text()'))?[0]", "@variables('varCol2')": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[1]//text()'))?[0]", "@variables('varCol3')": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[2]//text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@variables('varCol2')' as JSON property name: the property with name '' already exists.'."
It’s a complex one to be honest. I would suggest you strip back the select and gradually add in the different key/values as you test the flow. Hopefully you can find the cause and share the solution.
Thanks for informative video....need your support to decode the mail body table in to HTML view and some selected line need to agin send on mail via mail body by using power automate
Thank you so much for posting this great video! Not only it's super helpful, the quality is also top notch! Truly appreciate the clear and thorough explanation of each step & sharing of such great knowledge!
Hi, Thanks for sharing a very informative and helpful videos always. I would appreciate it if you could please help me out in following Query I have 300 emails in my inbox.Test (Folder). Each email has multiple table in email body. I wanted to extract first table from top. however, each table table's headers, row, columns are not same
Thank you so much for your kind words! I'm glad to hear that you find my videos informative and helpful. Regarding your query, that's not something I can help you with as it would require a fair bit of development time. Maybe post on the forum if you are looking to discuss ideas.
Hi Damien, excellent tutorial. It worked perfectly with the tip in the comment section to replace '/text()' to '//text()". I'm trying to, instead of making a new excel file with the table, update a table in an existing based on the data of the table in the mail via a key in the first column. I haven't figured out if that's possible yet but this seems like a first step to achieve that.
@@cMac4635 hey guys, are you able to instead of making a new excel file with the table, update a table in an existing based on the data of the table in the mail via a key in the first column.
Hi Damien - I know it's been a while since you released this (great video for us novices with Power Automate) but when I go to set it up, I get the following error "Unable to process template language expressions in action 'ComposeHTML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'Reference to undeclared entity 'nbsp'. Line 1, position 324.'. For reference, I am trying to convert a JIRA Subscription email to Excel to eventually export to PowerBI
@@comeartandplay2251 if you want to send me on a copy of the html table I could take a look but I can’t promise when. You should be able to use replace().
Wow, this was great, I really needed to work out how to do this. I'm fairly new to Power Automate, but you took me through so much that was new to me including Xpath and functions in such a short time and I actually got it to work Really appreciate it man.
Hi @Damobird365 Thanks for an awesome tutorial. I am getting one error at 'Add_a_row_into_a_table' action Error - Action 'Add_a_row_into_a_table' failed: A value must be provided for item. the error occurred due to a missing value for the 'item' parameter in the 'Add_a_row_into_a_table' action. In the action properties, the issue is with 'Row' field where i have parameter item() Could you please share how to fix it
Hi Damien, I have a question. How do I set the rows range when I don't have a fixed number of rows in my table? In your previous example, you showed how it works with 7 rows, but how do I handle it when I don't have a fixed number of rows? Please let me know if you have any suggestions or insights on this. I'd appreciate your help.
Hey @damoBird365 thanks for the spot-on content and detailed info. In the GetHTMLDataAsJSONArray > ComposeStart and END table will it be possible to extract table data from .html file attached on email or saved SharePoint/OneDrive.? Many thanks in advance
Great Video! Have you tested loading all stuff to the Office Script then do the job inside the script? Office Scripts are JavaScript natively so maybe its easier to access HTML objects.
Interesting idea Konrad. I’m a low coder, rather than a pro coder, so would need time to work that one out. Is that something you could put together? Office Scripts has quite a lot of uses beyond excel, like sorting an array or performing validation on a string with regex for instance. Would love for you to share your solution.
I'm very excited to start implementing this in my very first power automate flow (pretty ambitious I know coming from someone with outdated academic coding experience). It seems like I've hit a wall on the ComposeHTML step. I tried to do a simple xml(outputs('ComposeAddClosingTableTag')) operation but I get the following error? Hoping this is a simple fix. Thanks again Damo for putting together this video, it was most helpful out of the other ones I've seen out there! Unable to process template language expressions in action 'ComposeXML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'The 'img' start tag on line 1 position 7915 does not match the end tag of 'span'. Line 1, position 8063.'.
I am not sure to be honest, is your html valid? You could try something like validator.w3.org/#validate_by_input to ensure you have the opening/closing tags.
Hi Damien I followed all the steps but it gives me the error "The 'start index' for function 'substring' must be equal to or greater than zero and must be less than '1559' which is the length of the string." when I tested it, do you know how to fix it?
You will have passed in values that exceed the length of your string. Check your expression. Otherwise I would recommend asking on the forum powerusers.microsoft.com/
Hi it's Kanchan from India, I am receiving work mails containing html body and a few html tables. I want to extract data from html tables to Excel table. Now in my html table th (table header is not there) , all the table i in . I mean the first is the column header I need in my Excel table and second is the data in need in the raw below particular Excel table headers. This is my first time with power automate flows and I am in hell of confusion in solving this html table to Excel table query.
If you have no th, you can use first() and skip() learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#collection-functions
Any idea why i would get this error from ComposeEndOfTable? The length of substring can't be longer than '4745' which is the length of the source string.
Great tutorial... If possible someone can help me on the Select step? The table i receive by email has 2 empty lines in the middle, in HTML it gets  . Using xpath how can i get rid of this lines?
You probably either want to use replace() on the html to remove this prior to the select or use a filter array action to remove the rows with that condition.
Let's say emails are received regularly with different amount of rows each time. Would this flow no longer work as you need to specify how many rows the table has? I'm wondering how to make the flow dynamic so it can handle any amount of rows..thanks
This should work with any number of rows, you do however have to specify the columns. Please check out my latest video for a similar demo of xpath ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2kvSlh-Tvb4.html
Hello Could you please help me in creating this I want the outlook table rows data into different excel column i downloaded you github file also but its not working for me it will be really helpfull if you can help me on this
Oh my God! This is awesome! Really appreciate your video! It was so helpful! The way you used the dynamic header help me to solve my issues. However I am facing two problems when taking xpath(), 1. I have a column with % in it. (Eg:100%), but I only want to insert 100 to my database. When I am using text() I am getting entire value. 2. I have a date in one column, when I am trying to insert into SQL, it is showing error "OpenApiConnection" is not valid. The runtime value "29/04/2022" to be converted doesn't have the expected format 'String/date'. Could you please help in the above.
Hi Damien, Thanks for the amazing video. I created a workflow which writes html tables into a single Excel file using "add a row into a table action", it is working fine but leaving empty rows for every run to run leaving blank rows in between rows with data, could you please help me out with this error?
I am not sure, ultimately you want to explore your flow history. Does the array have empty objects. You could sort that by filtering potentially. I would recommend posting on the forum powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums
Hi Damien, Congratulations on the case. It's fantastic. I'm trying to create the table from a file of 17 columns and an average of 1800 rows and the process is taking an average of 3 and a half hours to complete. In the stage of composing the table, through the Xpath that is the bottleneck. Is there any way to improve this performance?
@@DamoBird365 Not. I'm using Select to compose the table by XPath. When I include only one column, it takes about 3 minutes to compose approximately 1800 rows. If I include 17, it's more than 3 hours and half. Could it be XPath?
Hi Damien, This was really helpful. Thank you for the video! I am stuck at one step where our html in the email has styling so when we try to get table data, the styling part also populated with it. In Xpather, this styling is not seen with the same code. Any guidance on how to solve this?
Good afternoon, Thank you so much for completing this video. It has been a tremendous help. I have completed all the steps in the video but am encountering a strange issue. I have spent two full days at work trying to solve without any resolution. I have an email that I have been testing on with 4 rows. When the flow runs it counts 4 rows but the first row is the first column header. It then counts row two correctly and so on. I have tried adjusting the rowcount minus and range but no success. Here is a text stripped copy of the html code from the composehtml output. Its like its missing the second all together. Line # Line Description BOC Qty. Unit Price Est. Extended Price Justification 1 TEST 254B 1 $0.00 $0.00 tEST1 2 PtEST 254B 1 $82.01 $82.01 nOTHING. 3 Primary 254B 1 $124.83 $124.83 nOTHING. 4 Primary: 254B 1 $82.01 $82.01 nOTHING gate.
Hi Damien, Great video! Thank you so much for posting this. I ran into a similar error as others have. My SelectRowFromHTML ran okay, but ComposeTableRow gave an error. “Unable to process template language expressions in action 'ComposeTableRow' inputs at line '0' and column '0': 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]//text()')}' as JSON property name: the property with name '[]' already exists.'.” Any advice is highly appreciated.
My guess is that the path you have provided is invalid and returns null. Start small and build this up one by one so that you understand it is working. [] would suggest null is being returned. Good luck 👍
Hi Damien, Many thanks for the reply. It worked for single but not multiple rows. Okay with { “ xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]//text()')“:”xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[1]//text()'))?[0]” } But if I add another row like the one below, the above error message comes up. {“ xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]//text()')“:”xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[1]//text()'))?[0]”, “ xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]//text()')“:”xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[2]//text()'))?[0]” } That can be rectified by having multiple composes, one for each td.
Great video! I appreciate you putting it together! Any advice on what to do when an email contains multiple tables? I've got an email with three or so tables and only need data from one.
@@DamoBird365 Thanks! does that basically involve changing ComposeStartofTable to something like: substring(triggerOutputs()?['body/body'],indexof(triggerOutputs()?['body/body'],'
@@justinwilcoxphotography2012 check out this point in the video and you’ll hopefully see the code highlighted onscreen ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-i4GHCGMAD88.html
@@DamoBird365 I have the same problem, I was also thinkting the same thing that: substring(outputs('ComposeStartOfTable'),0,indexof(outputs('ComposeStartOfTable'),'
This is brilliant. Matches my use case exactly - my company is getting spammed with emails (that contain a table of data) that we need to insert into a database. Thank you very much.
Hi Damien - it looks like "Add a row into a table" has changed since you made this video. It doesn't have "Row" as parameter anymore! Any ideas on how I can use this in apply to each? I regularly get emails with HTML tables with 34 rows that I want to automatically consolidate into a single excel file. I was going to skip the steps of creating a new Excel file and just apply to each > add a row into a table but now I can't figure out how to do that. Any suggestions would be very appreciated!
Hello Damien and thanks for your video.I would ask your help for what is happening to me: When the first compose try to get the data from the html email table it seems that Power Automate does a sort of conversion of the original data. For example, the original tag
@@ducacosworth so instead of using actions to process this programmatically, you could pass the html to a language model and ask it to extract as a json array. I’ve been thinking about this as a video. AI Builder has Prompts but you can also use GPT in Azure. Like this but not with an image pass the html: Automate OCR with GPT-4o and Power Automate ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-vE9If6_dO80.html
Hi Damien, thank you so much for sharing. I am working with larger data sets and Apply-to-each causes too many API calls. Adapted to my needs and really superfast.
Hi Damo Im trying with another html table and It is not possible to go further than the step compose html, the error message "Unable to process template language expreessions in action "Composehtml" inputs at line "0" and column "0". The template language function "xml" parameter is not valid. The provided value cannot be converted to xml:"Unexpected end of file has occurred. The following elements are not closed: td, tr, tbody, table. Line 1, position 773 The thing is when i try with the example of the video, the flow goes right, but when i tried with another email the resultd is error on that step. Any idea about this stuff? Thanks for the video
Hi Susitha, I think you’ll have to do a bit of detective work here, outside of Power Automate. Place the html output (prior to xml()) into a compose, run it and check the history output of the compose. Then copy the html into a html validation tool to see what’s missing. You must be missing tags.
Hi Arup, looks like an object. You could try adding ?[‘columnname’] on your expression. Might also need to use first or [0]. More on JSON here ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-G3Q1WuZTWuY.html
Hi Damien, great tutorial. I have got the flow working but I need advice on something. Do you know how you could make it instead of inserting rows based on column number you could insert them based on the column name ? I receive emails that don't always have the columns I need in the same column number everytime
hi very good video Damo. Im just getting the following error at Composexml stage: ' "Unable to process template language expressions in action 'ComposeHTML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML:"
Hi Damion, I am running into similar issue with the following message: "The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: ''1' is an unexpected token. The expected token is '"' or '''.". Any advice you can provide on this will be greatly appreciated. Thank you.
This is what I'm looking for. it's completely helping me to work on my project. But will this work when the rows changes for every mail or the rows should be fixed?
What flavor or Power Automate are you using, or required, for what you've demonstrated? I'm wanting to do this very thing, but the Power Automate Desktop available to me doesn't look like what you're using. Does this functionality require Permium?
Great videos again. Solving one of my new project issue. Great going. Tough i stuck at composeHTML where is shows "Reference to undeclared entity 'nbsp'. " error. I have put colgroup as well and still showing this error. Kindly help.
Nbsp is a Non-Breaking Space. Not sure if you can replace with ‘’ using replace() or if there is a better method. You’ll need to experiment on xpather as I don’t know the answer. Please share if you get a solution.
Thank you so much, Damien, this is a great video and channel. Really appreciate the effort you put into producing such comprehencive guides and taking the time to explain things! Im a total beginner, and this is my first power automate project, im about 5 hours in now and have the flow running completely without any errors (I had to do lots of modification to the table read and select sections as the data source tables i will be dealing with have spans and other elements). Your excellent tutorial helped me actually understand what i was doing as opposed to just giving me a working final product with no understanding. Xpather was so helpful. Really glad i found your content!. Im currently troubleshooting why the output excel files are coming up blank. I can see the correct number of rows and columns being created in the table, but all the headings, table content and sheet name arent being entered as configured. When i check the post-test screen, i can see the data from my table being listed correctly, so i think its a minor thing ive missed. Looking forward to cracking this last hurdle! Thanks again!
Amazing, really pleased this has helped. Power Automate returns null if an ? Is included and a value doesn’t exist. It’s possible that your expression for the values are all returning null, a typo or misunderstanding? Remove a ? And you will get an error that the key cannot be found during runtime. If you can see the data in your select from the xpath, you must have the expressions wrong for the add a row.
@@DamoBird365 Thanks for the input! I'd forgotten to include the span in the xpath (in the create table action). So you were spot on, without the span it was returning a null value (xpather to the rescue!).
In case anyone is wondering, here is my syntax for including the span in the xpath formula: //table[1]//tr[1]//td[2]//span/text()' My table looks like this: Pending Approval without including span, the xpath would return Null. I cant express how fun following along and troubleshooting this has been! Thanks again!
This video couldn't have come at a better time! I am needing to do exactly this with a series of emails and have been looking for a means to automate the process without using a third party's service. Thank you! I am brand new to Power Automate but managing to not get totally lost thanks to your instructions. :) I am having one bit of trouble, though: in a test run the flow is returning an error during the "Select Rows from HTML" phase ('Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}' as JSON property name: the property with name '' already exists.'.) Do you *happen* to know why that might be? I have tried to remove the select rows function as I don't need that anyway (I'm happy to do some data manipulation in Excel), but unfortunately my lack of skills then shows as I am not quite skilled enough to edit the following "FileContent" script to reflect removing the "select rows" function. Any thoughts you have would be very much appreciated, but thankyou for the great video regardless!
@@DamoBird365 Hi! Would there be any follow up regarding this? I am having the same issue highlighted by Ruth :( I really appreciate your easy to understand content.
@@nikkithanwin-200h7 I’ve not seen the problem myself so it is hard to comment. If you can get some screenshots to me and the sample html, I will try and have a look ideas@damobird365.com
Hello all, First of all thank you Damien, amazing solution! Were you able to solve this issue? I tried the pinned stretegy and it didn’t work. The first run always work, but when I send a second email I keep receiving this same error Ruth had. Thanks
Hi Damien, This below is the error I'm struggling with it in "SelectRowsFromHTML" in Filter Array. pardon for not having depth understanding, cause I am Finance professional. The execution of template action 'SelectRowsFromHTML' failed: The evaluation of 'query' action 'where' expression '{ "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[1]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[2]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[3]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table[1]//tr[', item(), ']/td[3]/text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}' as JSON property name: the property with name '' already exists.'.
This is almost exactly what I was looking for! Thank you so much! I have an issue though. I need to insert data to an existing excel file that is already formatted with the column headings I need. I can't find a way to populate new rows in that excel file with the data extracted with the "SelectRowsFromHTML" action. Could you please help me? Thank you
The basic way would be with add row to a table and loop through the select output for each object. The expression for each field in add row to table will be item()?['Fieldname'].
@@DamoBird365 Thank you so much! It now seems to be working fine! I have one more question. Would it be possible to prevent empty rows to be imported into the Excel table? Main concept: If no cells are populated in a row, do not import to Excel table. Nevertheless, if at least one cell in a row is populated, then import to Excel table. Thank you so much, you really helped me a lot with my work. Really appreciate it.
@@DamoBird365 I am still confused on this. I have an existing Excel file as well that I'd like to add rows to rather than creating a new file each time. I'm not understanding how to map out the values each column and exactly how to construct the expressions. Do I need to create variables for each value and use those? The catch is that there are two versions that are sent out of this table, and the wording of the headers in slightly different for each - however the data is the same essentially. Phone Number, First Name, Last Name, Reason for Call. However the header on version 1 might say "Customer's Phone Number"/"Reason for Call", while the second version says "Phone Number"/"Client Concern". The values are always in the same order/same columns though. With that being said, how would I map out the values for each column if the headers are worded slightly differently...Headers are always in row 1 though, and they are listed as rather than . LOL HELP PLEASE, I'M SO CLOSE TO FINISHING THIS!!!
@@lizhentschl6644 are you referring to the key names in the select? Could you just use an if expression for each key/column name depending on your version? It sounds like the value expressions are the same?
@@DamoBird365 So, in the email, it is a table with four columns and two rows, always. Column 1 will always be the phone number, 2 the first name, 3 the last name and the 4th the reason for call. However, the header names are worded slightly differently depending on who sent the email, (i.e. one email may have the 'phone number' header as "Phone Number" and another email might have the header as "Customer's Phone Number" so to reference the header names in the email table wouldn't work. Trying to figure out what to put in the "Add a Row into a Table" fields for each column to make sure the correct values populate in the correct columns, since the header names vary slightly per email. Therefore, my excel column names wouldn't always match the email table, however the columns are always in the same order (data from column 1 in email table will always go into column 1 in excel, essentially) Also, I keep running into an error on the ComposeHTML action: Unable to process template language expressions in action 'Compose4_-_ComposeHTML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'The 'col' start tag on line 1 position 447 does not match the end tag of 'colgroup'. Line 1, position 550.'. How do I go about this error and fixing this?
Hi Damien. I tried to follow nikki's instructions but i am still met with the error. Please help to advice and refine the explanation if possible. InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "@{xpath(xml(outputs('ComposeHTML')), '//table//tr[1]/th[1]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table//tr[1]/td[1]//text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')), '//table//tr[1]/th[2]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table//tr[1]/td[2]//text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')), '//table//tr[1]/th[3]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table//tr[1]/td[3]//text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')), '//table//tr[1]/th[4]//text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')), concat('//table//tr[1]/td[4]//text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')), '//table//tr[1]/th[2]//text()')?[0]}' as JSON property name: the property with name '' already exists.'.
I’m not sure with this one. To debug, I would start with the th values and fixed values, a string. See if it fails. Try with the first th and tr. see what happens. Then gradually build it up.
Hi Damien, this is incredible! I am a total rookie, but I am loving this!! I am hoping you might be able to assist me with the headers from my table. I have been googling for hours to find a work around with no luck. the rest of the email source code looks like the flow will work. I thought about just bypassing and creating the headers through a different flow, put I would love to learn about this if you have the time to share? Here is the formated xpath. Will you please let me know if this flow is still possible with this? Classification Do not show on list EDM Assigned Buyer Vendor Name Name Display Name EDM BSSR Notes Last Updated Safety Stock Available On Order Committed Wram regards, Dallas
Hi Damien, fantastic video and great intro to XPath - I didn’t know this function existed, and am excited about the possibilities. I’m running into severe performance issues however. I’m parsing an HTML table with 45 columns and ~1450 rows. Of the 45 columns, I only need to include ~10 in my array. All of the string actions are near-instantaneous despite the huge string size - but Select bogs down exponentially as I increase the number of table rows I feed in. With 50 rows, Select takes ~7 seconds; with 100 rows - a few minutes; with 500 rows - upwards of 30 minutes; and with all ~1450 rows, it ran for ~4 hrs and hasn’t finished before I terminated the run. What do you think is the bottleneck here - Select action or XPath function? Select has always been pretty fast in my experience, and Xpather.com chokes on a large XML - which makes me think XPath is the bottleneck? I could split the dataset into chunks of ~50 rows, loop though them, then merge the resulting arrays into one… but that would greatly increase complexity, not to mention the irony of throwing loops at performance issues. Any other suggestions?
It is xpath but also probably my design too. Try moving the xml() expression into a compose and call that compose from within the select. That will save a conversion for each loop and will hopefully see an improvement. I have seen performance issues with xpath however. Hence my video on joining arrays with string manipulation last week ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-R_bD8nAoJ8Y.html
@@DamoBird365 thank you! I actually did do xml() conversion in a Compose outside the Select initially, then tried your way within xpath() within Select map - didn’t seem to make any difference. Do you know if the “depth” of XPath expression makes any difference? My table isn’t particularly clean - so my XPath expression is more like //table//tr[]/td[]/span[]/p[]/text(). I wonder if it would make sense to clean up all the “junk” tags first to make XPath expression shorter?
Nice video ..i am working on similar requirement to read outlook email and read tabular data from email and insert in SharePoint list for each table row data other than header. Below is table details read from email. I am struggling read data ,could you please help here.TIA Error Type Instrument Name Back-up Plan Name Machine is offline for more than 20 days 6mmshp4ad08.test.com Machine is offline for more than 20 days RMMSHP4EAAF I am getting error at ComposeHTML steps Unable to process template language expressions in action 'ComposeHTML' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'Reference to undeclared entity 'nbsp'. Line 1, position 1783.'.
I think this would be better handled on the MS forum in terms of sharing the solution and letting others see how. There is a very similar question here powerusers.microsoft.com/t5/Building-Flows/Extracting-HTML-Table-from-email-to-Excel/m-p/1522850#M170301
Hi Damien, super cool video. I'm tapping into that Automate realm and it is really hard without guidances to create helpful and complex flows. Therefore really appreciate it. So I'm having issues with the SelectRowsHTML and xpath command. I get this error message while testing (my mail template has 5 columns): . The execution of template action 'SelectRowsFromHTML' failed: The evaluation of 'query' action 'where' expression '{ "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[1]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[1]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[2]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[3]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[3]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[4]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[4]/text()'))?[0]", "@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[5]/text()')?[0]}": "@xpath(xml(outputs('ComposeHTML')),concat('//table[1]//tr[',item(),']/td[5]/text()'))?[0]" }' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('ComposeHTML')),'//table[1]//tr[1]/th[2]//text()')?[0]}' as JSON property name: the property with name '' already exists.'. Can you advise what went wrong? Cheers!
Hi damian, im trying to parse and email that haves 10 html tables, and particularry i want to extract the data of the 8 table, do you have any idea of how can i get the first row of that table and the last row of that table, so i could make the loop properly, with no empty results, if you want i can contact you and send a sample of the email body. Thanks for the video
Use nthindexof learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#nthindexof and substring to get each open/close table tag and use in substring.
@@DamoBird365 I was testing with nthindexof and the result always gives an error, could you tell me how to formulate it? Until before the step ComposeCountRowsMinusOne I followed all the instructions in the video. So the last step of my flow is composehtml. Thanks for the reply and sorry for the inconvenience.