Forum Discussion
Comprateur
9 months 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...
- 9 months 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
9 months agoSuper User
The 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.