Selecting latest file from current and past months

[Anonymous]

Hello profs,

I'm new to Power BI, I'm having difficulties selecting the latest file for the current month. I've managed to select the latest file from past months but it seems that the code still returns only all files in the current month.

It should return 4/23/21, 5/20/21 and 6/14/21 files. 

Index
4/7/21
4/11/21
4/23/21
5/6/21
5/8/21
5/20/21
6/6/21
6/10/21
6/14/21

#"SelectLastDateofMonth" = Table.SelectRows(#"Changed Type8", (sel)=> if Date.IsInCurrentMonth(sel[Index]) then true else List.Max(List.Select(#"Changed Type8"[Index], each Date.Month(_)= Date.Month(sel[Index]) and Date.Year(_)= Date.Year(sel[Index])))=sel[Index]), 

[Anonymous · Accepted solution]

Hi @Anonymous 

You can groupby to find the max date for each month

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDXNzDRNzIwMlSK1YlWMjRE4RoZo3ANzPQNTJG4FihcIwNUWaBiMySTDVC5JghuLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}}),
    #"Inserted Month" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Column1]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"Month"}, {{"latest", each List.Max([Column1]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Month", {"Month"}, #"Grouped Rows", {"Month"}, "Grouped Rows", JoinKind.LeftOuter),
    Custom1 = Table.SelectRows( #"Merged Queries", each [Column1]=[Grouped Rows][latest]{0})
in
    Custom1

 or still use your way, 

#"SelectLastDateofMonth" = Table.SelectRows(#"Changed Type8", (sel)=> sel[Index]=List.Max(List.Select(#"Changed Type8"[Index], each Date.Month(_)= Date.Month(sel[Index]) and Date.Year(_)= Date.Year(sel[Index]))))

[Anonymous]

Piggy backing off this suggestion. I added the month name then sorted the files in Descending order using  Table.Buffer(Table.Sort(...)) then removed the duplicate values from the month column

[Anonymous]

Thank you!!