Forum Discussion
Comprateur
1 year agoRegular Visitor
Get email attachment with receivedtime
Hello, I'm learning Dataflow and Queries and I'm trying to load into a Lake an email with an excel attachment with the email received date. I cannot find the way to add the email received date as...
- 1 year ago
Here is an example where the email receive timestamp is blended into the table from the Excel attachment
let Source = Exchange.Contents(<email address>), Mail1 = Source{[Name="Mail"]}[Data], #"Filtered Rows" = Table.SelectRows(Mail1, each ([HasAttachments] = true)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Date.IsInPreviousNDays([DateTimeReceived], 1)), #"Expanded Sender" = Table.ExpandRecordColumn(#"Filtered Rows1", "Sender", {"Name", "Address"}, {"Name", "Address"}), #"Filtered Rows2" = Table.SelectRows(#"Expanded Sender", each ([Name] = <Sender>)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"DateTimeReceived", "Attachments"}), #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"Name", "Extension", "IsInline", "Size", "ContentType", "Last Modified", "AttachmentContent"}, {"Name", "Extension", "IsInline", "Size", "ContentType", "Last Modified", "AttachmentContent"}), #"Filtered Rows3" = Table.SelectRows(#"Expanded Attachments", each ([Extension] = ".xlsx")), AttachmentContent = #"Filtered Rows3"{0}[AttachmentContent], #"Imported Excel Workbook" = Excel.Workbook(AttachmentContent), Detail_Sheet = #"Imported Excel Workbook"{[Item="Detail",Kind="Sheet"]}[Data], #"Promoted Headers" = #table({"Received"},{{#"Filtered Rows3"{0}[DateTimeReceived]}}) & Table.PromoteHeaders(Detail_Sheet, [PromoteAllScalars=true]) in #"Promoted Headers"Note the last line before "in"
#"Promoted Headers" = #table({"Received"},{{#"Filtered Rows3"{0}[DateTimeReceived]}}) & Table.PromoteHeaders(Detail_Sheet, [PromoteAllScalars=true])Where you can see that a previous step #"Filtered Rows3" is referenced.
lbendlin
Super User
1 year agoThe Email Received date is part of the Email meta data, not the Excel meta data. You need to grab that independently, before you interpret the Excel data.