“I receive an email with data summary in a table, how can I parse the data from there using Power Automate flow?”
One of the most promoted use cases for Power Automate is email processing. You receive an email with an attachment and it’ll save the attachment to OneDrive. But often it’s not that simple. You might want to look for a specific attachment or a specific keyword in the subject, and even then just saving the attachment might be not enough. There’s also the email text which can contain relevant information. While it can be often extracted using the ‘HTML to Text’ action, it doesn’t work that well for tables.
How do you then extract data from a table?
Find the table
The first step to extract data from a table is to find the table. It doesn’t matter if it’s one or more tables, you must always find the right one, e.g. the one with ‘Table2’ in the header.
Take the whole email body, and split it by the ‘<table’ string, the opening tag of an HTML table.
split(triggerOutputs()?['body/body'],'<table')
The result won’t look pretty, but it doesn’t matter, it’ll give you each table as a separate item in an array.
Now you can search for the table based on some value. The whole table is a single item so you can use ‘Filter array’ action to search for that value (which must be unique!), e.g. ‘Table2’.
The result should be a single item with the table string.
Isolate the table
But at this moment it still contains more than just the table, it can contain all the other HTML tags before the next ‘<table’ string. To extract only the table, take the first output from ‘Filter array’. Use another split(…) expression, this time splitting by the closing table tag ‘</table>’, and take everything before that.
split(first(body('Filter_array')),'</table>')[0]
Return back the opening and closing tags that were removed with the split(…) expressions.
concat('<table', outputs('Compose_2'), '</table>')
That’ll give you a complete HTML table, convertible into XML with the xml(…) expression.
Once you have an XML, you can use xpath(…) to pick the desired value.
Summary
As you can see, it’s not complicated to parse data from an email with an HTML table using Power Automate. Using the HTML tags you can split(…) the email into pieces, extract and rebuild only the desired table, and extract the values directly with the xml(…) and xpath(…) expressions.