Forum Discussion
M Language dynamic max year value
Hi all - total M newbie here!
I think this ought to be a simple question to answer but I can't find anything online that helps me in this specific scenario.
I need to dynamically filter to max year value.
Here's the code as it is:
#"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "Order type", "Amount"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"Date"}, Calendar, {"Date"}, "Calendar", JoinKind.LeftOuter),
#"Expanded Calendar" = Table.ExpandTableColumn(#"Merged Queries", "Calendar", {"Financial Year"}, {"Calendar.Financial Year"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Calendar", each ([Calendar.Financial Year] = 2021))
I changed the last part to:
= Table.SelectRows(#"Expanded Calendar", each ([Calendar.Financial Year] = List.Max([Calendar.Financial Year])))
But I am getting this error:
How do I filter to the max value of [Calendar.Financial Year]?
Thank you
That was close! Your 'each' statement causes the second [Calendar.Financial Year] to be a single value, not the entire coulmn as a list. Here's how it will work:
#"Filtered Rows" = Table.SelectRows(#"Expanded Calendar", let latest = List.Max(#"Expanded Calendar"[Calendar.Financial Year]) in each [Calendar.Financial Year] = latest)
--Nate
2 Replies
That was close! Your 'each' statement causes the second [Calendar.Financial Year] to be a single value, not the entire coulmn as a list. Here's how it will work:
#"Filtered Rows" = Table.SelectRows(#"Expanded Calendar", let latest = List.Max(#"Expanded Calendar"[Calendar.Financial Year]) in each [Calendar.Financial Year] = latest)
--Nate
Cool! I was wondering how to set it as a variable.
Thank you for taking the time to answer, I really appreciate it.
