Extracting data from teams email to excel.

[Dash_01]

Hi,

I am required to create a system that extracts data from a table which is emailed to MS Teams. Then, use those extracted data in a table to create an excel sheet and display in it on PowerBI.

I'm having trouble extracting data especially within that email in ms teams and because its in a html format I believe. Don't intend for 3rd party software. Is there a solution about this?

for example

Name	Type	Present	Budgeting	Forecasting	Present vs. Budgeting	Forecasting vs. Budgeting
Rice	Cost	$1.00	$52300.00	$5.00	5.00%	0.50%

what i've achieved so far

[zaphod88]

You can use split for this. Unfortunately, it's a bit complex IMHO. I tried it myself:

Split code in Compose: 

@split(split(split(outputs('Html_to_text')?['body'],'</tbody>')[0],'<tbody>')[1],'</tr>')

Sub code in Do until:

@Sub(length(outputs('Compose')), 1)

Outputs in Do until:

@outputs('Compose')[variables('Count')]

The Do until is necessary as splitting by tags means that there is a last empty item in result. If you keep it, it will make the flow fail, so I am getting rid of it using the Count variable.

Split code in Compose 3 and 4:

@split(split(item(),'</td>')[0],'<td style="padding:5px">')[1]

@split(split(item(), '</td>')[1], '<td style="padding:5px">')[1]
As you see, just the first array index is changing - it' s the "columns" position in the array. You need as many indexes as there are columns in your HTML, beginning to count with 0 for the first index.

Things are working here:

[GeoffRen]

If you know how to use javascript, it will be cleaner to use Office Scripts, specifically Office Scripts with Power Automate. This is the 'Run Script' action on the Excel connector for Cloud Flows. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So you can parse the html and add it to the table in one step, you just have to implement that in code.

[Dash_01]

This requires javascript as well right? like to parse the data from each column of the table in the expressions tab

[zaphod88]

No, it's just the built-in expressions of Power Automate working here. No javascript knowledge needed.

[Dash_01]

quick question i keep getting a whole block of my message being displayed in the output... I'm quite lost 

[zaphod88]

Can you show me the whole flow and (if possible) the HTML you want to parse?

[Dash_01]

This is the code for the Compose - 

@split(split(split(outputs('Html_to_text')?['body'],'</tbody>')[1],'<tbody>')[1],'</tr>')

I tried to play around with the code and it worked but still then there were errors below during the Append_to_array_variable. i can't quite understand the error it's giving me... and below is the html i'd like to show in an excel sheet. I also provided a rough preview how it looks. 

<p><div>

<p>&nbsp;</p>

<table border="0" cellspacing="3" cellpadding="0" width="100%" style="width:100.0%">
<tbody>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Microsoft Budgets Report </span>
</p>
</td>
<td style="padding:.75pt .75pt .75pt .75pt">
<p align="right" style="text-align:right"><span style="font-family:&quot;Helvetica&quot;,sans-serif">May 15, 2021
</span></p>
</td>
</tr>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Microsoft Account 909384724723</span></p>
</td>
<td style="padding:.75pt .75pt .75pt .75pt"></td>
</tr>
</tbody>
</table>

<p><span style="color:">&nbsp;</span></p>

<table border="0" cellspacing="3" cellpadding="0" width="100%" style="width:100.0%">
<tbody>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Dear Microsoft Customer, </span>
</p>
</td>
</tr>
</tbody>
</table>

<p>&nbsp;</p>

<table border="0" cellspacing="3" cellpadding="0" width="100%" style="width:100.0%">
<tbody>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p><span style="font-size:12.0pt; font-family:&quot;Helvetica&quot;,sans-serif">You requested that we provide a weekly update on the status of the following budgets.
</span></p>
</td>
</tr>
</tbody>
</table>

<p>&nbsp;</p>

<table border="0" cellspacing="0" cellpadding="0" style="max-width:675.0pt; border-collapse:collapse">
<tbody>
<tr>
<td style="border:solid #CCCCCC 1.0pt; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Budget Name </span></p>
</td>
<td style="border:solid #CCCCCC 1.0pt; border-left:none; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif; color:black">Budget Type </span>
</p>
</td>
<td style="border:solid #CCCCCC 1.0pt; border-left:none; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif; color:black">Current </span></p>
</td>
<td style="border:solid #CCCCCC 1.0pt; border-left:none; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif; color:black">Budgeted </span>
</p>
</td>
<td style="border:solid #CCCCCC 1.0pt; border-left:none; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif; color:black">Forecasted </span>
</p>
</td>
<td style="border:solid #CCCCCC 1.0pt; border-left:none; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif; color:black">Current vs. Budgeted
</span></p>
</td>
<td style="border:solid #CCCCCC 1.0pt; border-left:none; background:#CCCCCC; padding:3.75pt 7.5pt 3.75pt 7.5pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif; color:black">Forecasted vs. Budgeted
</span></p>
</td>
</tr>
<tr style="height:9.0pt">
<td style="border:solid #CCCCCC 1.0pt; border-top:none; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><a href="https://clicktime.symantec.com/3MRtHaQBw36VQzH5v8w1ihD7Vc?u=https%3A%2F%2Fconsole.aws.amazon.com%2Fb... style="font-family:&quot;Helvetica&quot;,sans-serif">Monthly Cost
</span></a></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Cost </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$0.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$400.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$0.04 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">0.00% </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">0.01% </span></p>
</td>
</tr>
<tr style="height:9.0pt">
<td style="border:solid #CCCCCC 1.0pt; border-top:none; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><a href="https://clicktime.symantec.com/3J7E2fQg2NSqkg4JguX3NdQ7Vc?u=https%3A%2F%2Fconsole.aws.amazon.com%2Fb... style="font-family:&quot;Helvetica&quot;,sans-serif">Monthly Costs
</span></a></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Cost </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$28.12 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$744.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$748.70 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">3.78% </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">100.63% </span></p>
</td>
</tr>
<tr style="height:9.0pt">
<td style="border:solid #CCCCCC 1.0pt; border-top:none; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><a href="https://clicktime.symantec.com/3HjXnyVFrcgQY6M8Zq1t6re7Vc?u=https%3A%2F%2Fconsole.aws.amazon.com%2Fb... style="font-family:&quot;Helvetica&quot;,sans-serif">Monthly Costs
</span></a></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Cost </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$40.80 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$1,000.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$799.99 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">4.08% </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">80.00% </span></p>
</td>
</tr>
<tr style="height:9.0pt">
<td style="border:solid #CCCCCC 1.0pt; border-top:none; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><a href="https://clicktime.symantec.com/39WVoryoT2XY2kR7m2XXqBM7Vc?u=https%3A%2F%2Fconsole.aws.amazon.com%2Fb... style="font-family:&quot;Helvetica&quot;,sans-serif">Monthly Costs
</span></a></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Cost </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$220.54 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$8,300.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$5,369.40 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">2.66% </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">64.69% </span></p>
</td>
</tr>
<tr style="height:9.0pt">
<td style="border:solid #CCCCCC 1.0pt; border-top:none; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><a href="https://clicktime.symantec.com/3NRJkgYdb4veK4XCDpa69Xi7Vc?u=https%3A%2F%2Fconsole.aws.amazon.com%2Fb... style="font-family:&quot;Helvetica&quot;,sans-serif">NG Monthly Costs
</span></a></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Cost </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$0.49 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$10,700.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$9.83 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">0.00% </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">0.09% </span></p>
</td>
</tr>
<tr style="height:9.0pt">
<td style="border:solid #CCCCCC 1.0pt; border-top:none; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><a href="https://clicktime.symantec.com/3UUuna8zR6YP2F8HcS8D4TA7Vc?u=https%3A%2F%2Fconsole.aws.amazon.com%2Fb... style="font-family:&quot;Helvetica&quot;,sans-serif">Sprunk Monthly Costs
</span></a></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">Cost </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$1,003.17 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$25,000.00 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">$19,682.34 </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">4.01% </span></p>
</td>
<td style="border-top:none; border-left:none; border-bottom:solid #CCCCCC 1.0pt; border-right:solid #CCCCCC 1.0pt; padding:7.5pt 7.5pt 7.5pt 7.5pt; height:9.0pt">
<p><span style="font-family:&quot;Helvetica&quot;,sans-serif">78.73% </span></p>
</td>
</tr>
</tbody>
</table>

<p>&nbsp;</p>

</div>
<br clear="both">

---

[zaphod88]

Try to make the splits() single formulas. That's way better to handle to debug and see where it fails. 

Nevertheless, it will probably get pretty tricky setting it up the proposed way as there are many style arguments and several tables in your HTML. 

[Paulie78]

@Dash_01 this is somewhat tricky but perfectly possible. I can help you to build it. Would you mind either of the following:

• For me to produce a YouTube video on how to do it (and give you the flow after).
• Have a teams session with you and go through with you how to do it, record it and publish it to YouTube?

Let me know if either work for you.

[Dash_01]

Hi @Paulie78 , sure you can produce a Youtube video on it.  I think it might be useful for other people to learn from it as well :). I've also been following your other videos on the power automation but this does seem a bit different to construct. Also is it possible to not hard code this? Like so in the future if i intend to add more it can adapt..?