Load the latest file with a filename x and the latest file with a filename y

[Anonymous]

Hello

I want to load and edit the latest files in a folder that have a filename containing File1 or File2.

The folder may contain:

File1 (created 1/1/21)

File1 (created 2/2/21)

File2 (created 1/1/21)

File2 (created 2/2/21) 

I want to load the latest file with a filename containing File1 and the latest file with a filename containing File2.

Then, each of these two files will need to be loaded to a separate worksheet named File1 or File2 respectively.

Is there a neat way to do that? 

Thanks!

[mahoneypat · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsvMSTVU0lEy1DfUNzIwAjGdMvMSiyqVYnWQZY1wyBoBhYwIyOIy2RgoZExAFsPkWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FileName = _t, ModifiedDate = _t, Content = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}, {"ModifiedDate", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([FileName] = "File1" or [FileName] = "File2")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"FileName"}, {{"AllRows", each _, type table [FileName=nullable text, ModifiedDate=nullable date]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {"AllRows", each Table.Max(_, "ModifiedDate", Order.Descending)}),
    #"Expanded AllRows" = Table.ExpandRecordColumn(Custom1, "AllRows", {"ModifiedDate", "Content"}, {"ModifiedDate", "Content"})
in
    #"Expanded AllRows"

Pat