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
You can use split for this. Unfortunately, it's a bit complex IMHO. I tried it myself:
Split code in Compose:
Split code in Compose 3 and 4:
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.
This requires javascript as well right? like to parse the data from each column of the table in the expressions tab
No, it's just the built-in expressions of Power Automate working here. No javascript knowledge needed.
quick question i keep getting a whole block of my message being displayed in the output... I'm quite lost
Can you show me the whole flow and (if possible) the HTML you want to parse?
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> </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:"Helvetica",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:"Helvetica",sans-serif">May 15, 2021
</span></p>
</td>
</tr>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p><span style="font-family:"Helvetica",sans-serif">Microsoft Account 909384724723</span></p>
</td>
<td style="padding:.75pt .75pt .75pt .75pt"></td>
</tr>
</tbody>
</table>
<p><span style="color:"> </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:"Helvetica",sans-serif">Dear Microsoft Customer, </span>
</p>
</td>
</tr>
</tbody>
</table>
<p> </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:"Helvetica",sans-serif">You requested that we provide a weekly update on the status of the following budgets.
</span></p>
</td>
</tr>
</tbody>
</table>
<p> </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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",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:"Helvetica",sans-serif">78.73% </span></p>
</td>
</tr>
</tbody>
</table>
<p> </p></div>
<br clear="both">
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.
@Anonymous this is somewhat tricky but perfectly possible. I can help you to build it. Would you mind either of the following:
Let me know if either work for you.
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..?
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
71 | |
27 | |
21 | |
15 | |
12 |
User | Count |
---|---|
141 | |
42 | |
42 | |
34 | |
30 |