Forum Discussion
Selecting latest file from current and past months
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]),
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 Custom1or 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]))))
3 Replies
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 Custom1or 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]))))