Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
plutoforever
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 (:

 

let
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],
{{"col1","td:nth-child(1)"},
{"col2","td:nth-child(2)"},
{"col3","td:nth-child(3)"},
{"col4","td:nth-child(4)"},
{"col5","td:nth-child(5)"}},
[RowSelector="tr"]),
#"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}})
in
#"Changed Type1"

2 REPLIES 2
some_bih
Super User
Super User

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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
Html.Table([HtmlBody.3],
{{"Col1","td:nth-child(1)"},
{"Col2","td:nth-child(2)"},
{"Col3","td:nth-child(3)"},
{"Col4","td:nth-child(4)"},
{"Col5","td:nth-child(5)"}},
[RowSelector="TABLE > * > TR"])),

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors