M Language dynamic max year value

[C4L84]

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

[C4L84]

Cool! I was wondering how to set it as a variable.

Thank you for taking the time to answer, I really appreciate it.

[Anonymous · Accepted solution]

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