Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]),
Solved! Go to 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]))))
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]))))
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
Thank you!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.