Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Delete rows in M Query Based on Max Year

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.

Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    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/

     

     

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    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/

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.