Delete rows in M Query Based on Max Year

[luvagoldenk9]

Hello!

I have a table that has Year / Customer Name recorded for a transaction:

2022     Customer A

2022     Customer B

2022     Customer C

2021     Customer A

2020     Customer B

I only need the 3 2022 records as that is the max year and do not want to hardcode it for a specific year. In M query, is there a way to find the max Year and only keep those current transactions?

Thank you in advance.

[Ashish_Mathur]

Hi,

This M code works as well

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Customer", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"Max Year", each List.Max([Year]), type text}}),
    Joined = Table.Join(Source, "Customer", #"Grouped Rows", "Customer"),
    #"Added Custom" = Table.AddColumn(Joined, "Test", each [Year]=[Max Year]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Test] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Max Year", "Test"})
in
    #"Removed Columns"

Hope this helps.

[VahidDM · Accepted solution]

Hi @luvagoldenk9 

You can use List.Max to find the MAX value in the Year column, so the M code for your question can be as follow:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlLSUXIuLS7Jz00tUnBUitXBFHXCKuoMEzXEaoIBmgmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Customer = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Customer", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = List.Max(#"Changed Type"[Year])))
in
    #"Filtered Rows"

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = List.Max(#"Changed Type"[Year])))

Sample file attached.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[luvagoldenk9]

Thank you VahidDM! Adding that one statement worked. Have a great day!