Power BI to Convert HTML to Plain Text

L

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,171
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I'm using Power BI Desktop and inside Power Query I have the following HTML code (see very bottom) that I'm trying to convert to plain text. I added a custom column and used the following formula to covert the HTML to text but nothing shows inside the table after the custom column has been added.

=Html.Table([HtmlBody],{{"Text",":root"}})

Is the below code a real HTML code? Does it make sense? I thought I should see the field names/contents like "Location", "Department" (similar to how it shows in my Outlook email) somewhere inside the code but it doesn't appear so. I'm not sure what the source for "table.gridTable" is below either.

Please advise.


HTML: 
<!DOCTYPE html><html><head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"><style type="text/css">
        table.gridTable {
          font-family: Arial, Helvetica, sans-serif;
            font-size:13px;
            border: 2px solid black;
            border-collapse: collapse;
        }
        table.gridTable td, th {
            border: 2px solid black;
            padding: 8px;  
        }

        tr.noBorder td{ border-top: 0px; border-bottom:0px; padding: 8px;}
        td.label { font-weight:bold; width:160px;}
        td.label2{ font-weight:bold; w
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
I was able to display the plain text of the above code in Power BI desktop. The code is just a part of the email but not the table that I want to extract within the body of the email.

I'm basically trying to have Power Query read an Outlook email and extract the fields which appear to be in HTML format because my default Outlook settings is HTML and I can see a table outline when I highlight the fields I'm interested in, whereas Plain text does not do that. I even changed the Email Security settings of Outlook to "read all standard mail in plain text" but Power Query still does not read the fields I want (it does not display it all).

I'm guessing the fields I want to extract is a HTML in a nested table, a table within a table which is why Power Query cannot read this type of format and does not display those fields.

Any ideas or tips? Please advise
 
Last edited:
Upvote 0
Furthermore, if I inspect the HTML code of Outlook web version (office.com) I can see the table elements in the code which I need to extract. However in the Outlook desktop version Power Query cannot read it. Is there a reason? Perhaps if I connect to the web I can extract those fields?

Is there a way to connect Power Query to the Outlook web version?
 
Upvote 0
I was able to find found out how to view the full HTML code of an email from Outlook desktop. The fields that I need to extract but Power Query (with the red RGB tags which I put next to such as Location, Ship to, Ordered By fields etc) is not reading the email body, only part of the code that I showed in Post #1

HTML: 
<!DOCTYPE html><html><head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"><style type="text/css">
        table.gridTable {
          font-family: Arial, Helvetica, sans-serif;
            font-size:13px;
            border: 2px solid black;
            border-collapse: collapse;
        }
        table.gridTable td, th {
            border: 2px solid black;
            padding: 8px;    
        }

        tr.noBorder td{ border-top: 0px; border-bottom:0px; padding: 8px;}
        td.label { font-weight:bold; width:160px;}
        td.label2{ font-weight:bold; width:250px;}
        .infoTable{border-style:none;border-collapse: collapse;}
        .infoTable td{border-style:none;padding:1px 0px;}
        .notificationUrlLink{
            font-family: Arial, Helvetica, sans-serif;
            font-size:27px;
            text-decoration:underline;
            color: #3089C5;
            font-weight: bold;
        }
        .notificationUrlLinkDiv{
            margin-top: 15px;margin-bottom: 15px;
        }
    </style>
</head>
<body>
<div style="margin-left:35px;margin-right:35px;">
<div style="width: 70%;margin:auto;font-style: italic; color:#717173">To ensure delivery of McDonalds emails to your inbox, please add
<a href="mailto:noreply@mcdonalds.com">noreply@mcdonalds.com</a> to your Email Address Book or Safe Senders List.<br>
If you no longer wish to receive email notification when you place an order through mcdonalds.com, simply go to your profile settings and unselect the &quot;Notify me when any order status changes&quot; option.'
</div>
<div style="margin-top: 35px;margin-bottom: 35px;"><img src="http://www1.mcdonalds.com/order/images/logos/logo_login_usf.jpg">
</div>
<!-- Link in email -->
<div class="notificationUrlLinkDiv"><a class="notificationUrlLink" href="https://gcc02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww3.usfoods.com%2Forder%2Ffaces%2Fpub%2Fsec%2FecPortal.jspx%3FORDER_ID%3DK67447TK869A5020W88Y&amp;data=05%7C01%7C%7Cbd9f125005eb49d1350b08da96bcc373%7Ce95f1b23abaf45ee821db7ab251ab3bf%7C0%7C0%7C637988034729095203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=xbr0yaJZudmEBK3THEH22s99Qf%2Fx8r2kqb%2FdgncU7D4%3D&amp;reserved=0" originalsrc="https://www3.mcdonalds.com/order/faces/pub/sec/ecPortal.jspx?ORDER_ID=K67447TK869A5020W88Y" shash="mDlqO4KzDTDtZzJpHfvp3gX+sm3I/sXyR1MxqeJzFYbhIBaMfOBpKlyTmFj6NWsiUL5nG6gVQYLd1P1jHItxhMJltghrR5y/Jvs/hgIQBpac4Fmb/XMK6DYr3QGV6SjBeKDz1ptPp3Ibbp3OV7cTdejA0MAg7VkBu17nlU4CAiM=">View My Order</a>
</div>
<div style="color: #CE4620;font-size: 24px;font-weight: bold;font-style: italic;margin-bottom: 20px;">
Order Submitted</div>
<div id="emailBody">
<p>Thank you for your recent order placed with mcdonalds.com. This email notice was sent to you based on your preferences settings with mcdonalds.com whenever an order is placed for this customer:
</p>
<table class="infoTable">
<tbody>
<tr>
<td class="label"><b>Details of your order:</b></td>
<td>&nbsp;</td>
</tr>
<tr>
<td>[COLOR=rgb(184, 49, 47)]Location:[/COLOR]</td>
<td>MCDC #301Los Angeles (33495720)</td>
</tr>
<tr>
<td>Department:</td>
<td>DIETARY&nbsp;(10)</td>
</tr>
<tr>
<td>[COLOR=rgb(184, 49, 47)]Ship&nbsp;To[/COLOR]:</td>
<td>2100 Sunset Blvd<br>
(120-PAD)<br>
Los Angeles, CA 9020100000 </td>
</tr>
<tr>
<td>[COLOR=rgb(184, 49, 47)]Ordered&nbsp;By:[/COLOR]</td>
<td>Joe Smith</td>
</tr>
<tr>
<td>Special&nbsp;Instructions:</td>
<td>NA CUSTOMER. DRIVER PLEASE CALL 30 MIN BEFORE DELIVERY TO MINIMIZE DELAY.</td>
</tr>
<tr>
<td>[COLOR=rgb(184, 49, 47)]Delivery&nbsp;Date:[/COLOR]</td>
<td>04/1/2020</td>
</tr>
<tr>
<td>[COLOR=rgb(184, 49, 47)]Order&nbsp;Number:[/COLOR]</td>
<td>375500</td>
</tr>
<tr>
<td>Customer&nbsp;PO#:</td>
<td>C20010-91422</td>
</tr>
<tr>
<td>Order&nbsp;Guide:</td>
<td>374976</td>
</tr>
<tr>
<td>Order&nbsp;Status:</td>
<td>Submitted</td>
</tr>
<tr>
<td>Requested&nbsp;Delivery&nbsp;Date:</td>
<td>09/16/2022</td>
</tr>
</tbody>
</table>
<br>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
839
gvsumich
G
D
  • Question
Help to remove Garbage HTML tags
Replies
1
Views
628
MCLIFTO8
MCLIFTO8
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
H
  • Question
Power Pivot Formula not working
Replies
4
Views
1K
howard
H
2
  • Solved
add borders to HTML email
Replies
4
Views
4K
288enzo
2

Forum statistics

Threads
1,215,092
Messages
6,123,064
Members
449,090
Latest member
fragment

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top