cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dash_01
Frequent Visitor

Extracting data from teams email to excel.

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

 

NameTypePresentBudgetingForecastingPresent vs. BudgetingForecasting vs. Budgeting
RiceCost$1.00$52300.00$5.005.00%0.50%
       
       
       
       
       

 

what i've achieved so far

 

Screenshot (33).png

 

 

10 REPLIES 10
zaphod88
Responsive Resident
Responsive Resident

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

zaphod88_0-1620637670880.png

 

zaphod88_1-1620637714841.png

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.
 

zaphod88_3-1620637797113.png

 

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:

zaphod88_2-1620637767963.png

 

GeoffRen
Microsoft
Microsoft

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
Frequent Visitor

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

zaphod88
Responsive Resident
Responsive Resident

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

Dash_01
Frequent Visitor

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

 

Screenshot (34).png

zaphod88
Responsive Resident
Responsive Resident

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

Dash_01
Frequent Visitor

Screenshot (38).png

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. 

 

Screenshot (39).png

 

 

<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
Responsive Resident
Responsive Resident

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
Super User III
Super User III

@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
Frequent Visitor

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..?

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,609)