Forum Discussion

Comprateur's avatar
Comprateur
Regular Visitor
9 months ago
Solved

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...
Dataflow
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    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's avatar
lbendlin
Super User
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.

Comprateur's avatar
Comprateur
Regular Visitor
9 months ago

Hi, 

I was going to the work direction trying to use parameters or references. 

It kind of works, but I have 2 additional questions.

How can I retrieve all sheets in a workbook using 

Detail_Sheet = #"Imported Excel Workbook"{[Item="Detail",Kind="Sheet"]}[Data],

And second question, how can you get all line from the excel filled with the Received time? In order to always know the datetime received when I analyse my Excel data at later stage.

 

thanks, 

  • lbendlin's avatar
    lbendlin
    Super User
    9 months ago

    1. You would add steps to enumerate all sheets, and concatenate them if they have the same structure

    2. Instead of adding the value as a row, add it as a column.

    • Comprateur's avatar
      Comprateur
      Regular Visitor
      9 months ago

      Thanks, 

      not sure that what you ment but I have been able by adding a new column to the table extracted from excel. 

      Table.AddColumn(Table.PromoteHeaders(#"Filtered rows 3", [PromoteAllScalars = true]) ,"Received", each #"Expanded Attachments"{0}[DateTimeReceived], type date)

       For having all sheets yes they are in the same format and I have expanded Data from the Excel and it works. 

      Thanks a lot for your help and patience.