Sorting issue in Power Query editor Combine files

[Anonymous]

Hi,

I am struggling with this below simple issue. This report was working fine until last week.

This report simply extracts the latest stock summary report (csv file) from a email folder.

-------------------------------

let
Source = Exchange.Contents("xxxx@abcd.com"),
Mail1 = Source{[Name="Mail"]}[Data],
#"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Inbox\3PLDockets\3PLReports\")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.Contains([Subject], "WIMS:DE2600:Stock Summary")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows1",{{"DateTimeReceived", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Attachments"}),
#"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", {"AttachmentContent"}, {"AttachmentContent"}),
#"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([AttachmentContent])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}})
in
#"Changed Type"

-------------------------------

No issues until below step.

As seen below it is the latest file (file name : Stock_Summary_DE2600_20210722050737.csv date in red)

The file is latest in #"Kept First Rows" step as well.

But in below #"Removed Other Columns" step the file is not the latest file, it is 01-Jan-2021 file.

What i noticed is this is the first row of result set in step #"Filtered Rows1", which is before sorting the rows (find below).

Please help me resolve the issue or is there is any other way to extract attachments from email.

Thank you in advance.

Regards,

Koushik.

[v-luwang-msft]

Hi @Anonymous  ,

The following two articles describe in detail how to extract attachments from emails, you can follow the steps in the articles and try them step by step based on your own email：

https://radacad.com/import-email-attachments-directly-into-a-power-bi-report-using-power-query 

https://blog.crossjoin.co.uk/2018/06/01/email-attachments-power-bi/ 

Wish it is helpful for you!

Best Regards

Lucien

[Anonymous]

Hi Lucien,

Thank you for the response.

I tried following the steps mentioned in below article again now and i am facing same issue. Infact i used the below as reference when i initaly did report few months back. It worked fine untill few days back and it was failing when i had to refresh another data file is same PowerBI file.

https://radacad.com/import-email-attachments-directly-into-a-power-bi-report-using-power-query

Now i created new PBIX file only to extract attachments by following exact steps mentioned in article above, but not sure why as soon as i remove other columns it is not the latest email anymore.

But, I tried not using "Removed Other Columns" step and expanded the attachment in same row and content of latest email attachment is extracted now.

Thank you once again.

Regards,

Koushik.