Power Query filter two lists by latest date

[Anonymous]

Dear All,
I am new to PowerBI and after playing around for 2 days and having fun, I face my first challenge.

I have connected a SharePoint Folder, with tons of files in it, where around 15 different excel-inventory-lists are replaced weekly by the same name but with a different date. Unfortunately, I do not have the permission to directly connect to the Database and therefore I have to rely on the Excel Sheets, I have access to.

I have the following scenario: 

Excel files beginning with a name and followed by a date.

A-04-04-2022.xlsx

B-04-04-2022.xlsx

C-04-04-2222.xlsx

together with the old ones:

A-28-03-2022.xlsx

B-28-03-2022.xlsx

C-28-03-2022.xlsx

The challenge for me now is, how can I filter in Query Editor, that I receive List A & B which are by date the newest one.

I could figure out, how to do the filtering for the lists itself:

= Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Name], "A") or Text.StartsWith([Name], "B"))

But now I need to align the formula on such way, that from the Date Modified, I get the latest list from both files but I fail with either ending up with nothing or only with one List.

I tried several ways and either ended up with only one list or as in the below example with nothing.

= Table.SelectRows(Source, let latest = List.Max(Source[Date modified]) in each Text.StartsWith([Name], "A") and [Date modified] = latest or Text.StartsWith([Name], "B") and [Date modified] = latest)

I am sure, there is a way, to do so, I just have no clue how.

Can someone please help and guide me?

Thank you very much for your support,

Mike

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctQ1MAEhIwMjI72KnOIKpVidaCUnrKLOMFEjZFFHXSMLXQNjDBOwiTpjisYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "A") or Text.StartsWith([Name], "B")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "DateExtracted", each Date.FromText(Text.BeforeDelimiter(Text.AfterDelimiter([Name],"-"),"."))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filter", each [DateExtracted]=List.Max(#"Added Custom"[DateExtracted])),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([Filter] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"DateExtracted", "Filter"})
in
    #"Removed Columns"

[Anonymous]

Hello @Vijay_A_Verma,
Thank you so much for your reply and sorry for my late reply. I didn't see your message, my bad. I will try that.