cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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.

Anonymous
Not applicable

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.

Anonymous
Not applicable

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?

Anonymous
Not applicable

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

@Anonymous 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.

Anonymous
Not applicable

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Top Solution Authors
Users online (2,081)