Power Automate Flow runs successfully in Test but when triggered by Email Fails: The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression

[Ishmale]

Hello All,

I am looking for help understanding why my Flow fails only when in production. I have created a flow that waits for an Email to be received daily and then extracts the HTML table out of the email then appends this information to an excel table I have located in a sharepoint folder.

Issue:

When I test my flow it always results in success and data is added to my excel document. Unfortunately, every morning I check my flow now that I am using it in production it fails every morning with the following error:

The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{
"@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[1]/p[1]/b[1]/span[1]/text()')?[0]}": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[1]/p[1]/span[1]/text()'))?[0]",
"Stock Diff": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[2]/p[1]/span[1]/text()'))?[0]",
"@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[3]/p[1]/b[1]/span[1]/text()')?[0]}": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[3]/p[1]/span[1]/text()'))?[0]",
"@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[4]/p[1]/b[1]/span[1]/text()')?[0]}": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[4]/p[1]/span[1]/text()'))?[0]",
"@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[5]/p[1]/b[1]/span[1]/text()')?[0]}": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[5]/p[1]/span[1]/text()'))?[0]",
"@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[6]/p[1]/b[1]/span[1]/text()')?[0]}": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[6]/p[1]/span[1]/text()'))?[0]"
}' failed: 'Unable to evaluate template language expression '@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[3]/p[1]/b[1]/span[1]/text()')?[0]}' as JSON property name: the property with name '' already exists.'.

Once again it passes without issue during Testing.

Output of Close HTML Table with Tag:

<table><tbody><tr class="titles"><td class="String">Product Id</td><td class="Number">Stock Diff (Click - Autostore)</td><td class="Number">Click Stock</td><td class="Number">Autostore Stock</td><td class="String">Depot</td><td class="String">Date</td></tr><tr><td class="String">139881</td><td class="Number">-1</td><td class="Number">21</td><td class="Number">22</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">14437985</td><td class="Number">60</td><td class="Number">60</td><td class="Number">0</td><td class="String">37</td><td class="String">01-SEP-2023</td></tr><tr><td class="String">166353A1</td><td class="Number">-2</td><td class="Number">76</td><td class="Number">78</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">280161</td><td class="Number">1</td><td class="Number">11</td><td class="Number">10</td><td class="String">37</td><td class="String">07-SEP-2023</td></tr><tr><td class="String">42574911</td><td class="Number">-1</td><td class="Number">30</td><td class="Number">31</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">48065388</td><td class="Number">-1</td><td class="Number">142</td><td class="Number">143</td><td class="String">37</td><td class="String">12-AUG-2023</td></tr><tr><td class="String">48188864</td><td class="Number">-3</td><td class="Number">0</td><td class="Number">3</td><td class="String">37</td><td class="String">30-JUN-2023</td></tr><tr><td class="String">4894532</td><td class="Number">-10</td><td class="Number">0</td><td class="Number">10</td><td class="String">37</td><td class="String">12-AUG-2023</td></tr><tr><td class="String">500352447</td><td class="Number">-1</td><td class="Number">38</td><td class="Number">39</td><td class="String">37</td><td class="String">12-AUG-2023</td></tr><tr><td class="String">5109356</td><td class="Number">-1</td><td class="Number">0</td><td class="Number">1</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">770210</td><td class="Number">-5</td><td class="Number">0</td><td class="Number">5</td><td class="String">37</td><td class="String">20-JUL-2023</td></tr><tr><td class="String">84314731</td><td class="Number">-31</td><td class="Number">0</td><td class="Number">31</td><td class="String">37</td><td class="String">27-JUL-2023</td></tr><tr><td class="String">86018406</td><td class="Number">95</td><td class="Number">188</td><td class="Number">93</td><td class="String">37</td><td class="String">07-SEP-2023</td></tr><tr><td class="String">86537325</td><td class="Number">-1</td><td class="Number">0</td><td class="Number">1</td><td class="String">37</td><td class="String">07-SEP-2023</td></tr><tr><td class="String">87301324</td><td class="Number">-1</td><td class="Number">82</td><td class="Number">83</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">87301564</td><td class="Number">-13</td><td class="Number">203</td><td class="Number">216</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">87623420</td><td class="Number">-4</td><td class="Number">348</td><td class="Number">352</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">E9NND797AA</td><td class="Number">-1</td><td class="Number">187</td><td class="Number">188</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">MT40029224</td><td class="Number">-5</td><td class="Number">40</td><td class="Number">45</td><td class="String">37</td><td class="String">16-AUG-2023</td></tr><tr><td class="String">MT40214684</td><td class="Number">-2</td><td class="Number">364</td><td class="Number">366</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">SBA050209004</td><td class="Number">-1</td><td class="Number">18</td><td class="Number">19</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr><tr><td class="String">SBA340990440</td><td class="Number">-1</td><td class="Number">81</td><td class="Number">82</td><td class="String">37</td><td class="String">15-AUG-2023</td></tr></tbody></table>

Any support would be very appreciated. 

[wskinnermctc]

I'm more curious how you tested the flow without sending emails? Were you just using a text variable or something?

It looks like the column name is not getting created. Or it is getting created as '' and then the next column name is not getting created because there is already a column named '' 

(I'm guessing the culprit causing the problem will be the column "Stock Diff (Click - Autostore)" since that is the one changing.)

Can you show the Select as well as the previous compose steps?

[Ishmale]

Hello,

I did use an email. I forwarded the email to myself for the test and no issues

[Ishmale]

[wskinnermctc]

What is the range expression of the select? It looks like the select is only getting the numbers but no other values.

[Ishmale]

Its dynamic, based on the number of rows in the HTML Table

[wskinnermctc]

Something must be going wrong in the expression where [',item(),']

"@{xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),'//table[1]//tr[1]/td[1]/p[1]/b[1]/span[1]/text()')?[0]}": "@xpath(xml(outputs('CloseHTMLTableUsingEndOfTableTag')),concat('//table[1]//tr[',item(),']/td[1]/p[1]/span[1]/text()'))?[0]",

[Ishmale]

Ok, what could it be since the same email works in test mode with no issues? This is why I am at a lose because it works perfectly fine until the auto generated email gets sent in the morning but when I forward it to my self for the test the issue does not re-occur.

[wskinnermctc]

Is there something that is extra in the email body that you maybe can't easily recognize? 

Like when people have signatures and pictures in the bottom of an email it can mess up some flow actions if not accounted for.

So when you forward the email to yourself, is it removing something or clearing something that is in the original auto email?

I know I'm asking you to look for something you can't see. But I would try to investigate the differences in the messages. Possibly due to the title "Stock Diff (Click - Autostore)" if that is a hyper link. The hyperlink might have additional characters for security or something in the original email might be adjusted when you forward to yourself.

I don't know exactly what that would be, but if (Click - Autostore) is a hyperlink then it might be the problem.

[hrangel-gg]

The xpath expression in the Select action is returning "" for more than one field, thus the error message. It might be a difference in the HTML structure when you first send the email and forward it.

Add a compose to log the HTML output from the email before the Select and doublecheck that with your xpath.

[Ishmale · Accepted solution]

Thank you, In the future I will check this. So I needed to get the project completed I created a workaround. Since I know the column names will not change, instead of trying to dynamically get the names from the HTML Table I just mapped them manually in "Select" to the names that I know they will be which also matches the column names that are in my excel Table. Doing it this way has allowed my flow to run in Production successfully.