Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Help with adding date to html.table M function when scraping Outlook email

I have created a Microsoft Exchange connection in PowerBI to be able to scrape an HTML table each day from a specific Outlook email I receive.  I am able to successfully extract the table into an actual table format I can then join to using Html.table.  However, when I create this new Html.table I lose the DateTimeSent column showing when the email came in.  How would I rejoin this to the end table so the end user can filter on the date to show just the data from an email from a specific day?  Here is the M code I am using (:


Source = Exchange.Contents(""),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each Text.Contains([Subject], "Optigistics Today - Error")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Folder Path] = "\Inbox\")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Sender", "DisplayTo", "DisplayCc", "ToRecipients", "CcRecipients", "BccRecipients", "DateTimeReceived", "Importance", "Categories", "IsRead", "HasAttachments", "Attachments", "Preview", "Attributes", "Id"}),
#"Expanded Body" = Table.ExpandRecordColumn(#"Removed Columns", "Body", {"TextBody", "HtmlBody"}, {"TextBody", "HtmlBody"}),
#"Cleaned Text" = Table.TransformColumns(#"Expanded Body",{{"HtmlBody", Text.Clean, type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Cleaned Text", "HtmlBody", Splitter.SplitTextByDelimiter("<div class=MsoNormal align=center style='text-align:center'><hr size=2 width=""100%"" align=center></div>", QuoteStyle.None), {"HtmlBody.1", "HtmlBody.2", "HtmlBody.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HtmlBody.1", type text}, {"HtmlBody.2", type text}, {"HtmlBody.3", type text}}),
#"HTML Table" = Html.Table(#"Changed Type"{0}[HtmlBody.3],
#"Promoted Headers" = Table.PromoteHeaders(#"HTML Table", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Error Code", Int64.Type}, {"Severity", type text}, {"ID", Int64.Type}, {"Customer Number", Int64.Type}, {"Error Message", type text}})
#"Changed Type1"

Memorable Member
Memorable Member

Hi @plutoforever before any changes, backup your work and in row 7 above part try to "DateTimeReceived"  delete and leave just sing comma between previous and next columns

This column is only meaningful by search for words date; time; receive

I hope this help

Sarajevo, Bosnia and Herzegovina

Thanks for your response.  I was actually able to achieve what I needed embedding the Html.Table inside a Table.AddColumn which created a new table column that I could expand while keeping all other columns from the email including the email date one.  The added benefit is with the following code the "each Html.Table" it brought in all new emails where just the "Html.Table" only brought in the most recent.


#"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom2", each
[RowSelector="TABLE > * > TR"])),

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors