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.

Comprateur's avatar
Comprateur
Regular Visitor
9 months ago

Thanks, 

I think I understand what you are saying ", doesn't have to be the exact previous step. You would need to use the advanced editor though as this cannot be done in the UI " But I don't know how to extract that information at the a particular step and reuse it late. 

Again thanks for your help.

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.