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...
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]))))
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]))))Thank you!!
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