Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
2 years ago
Solved

Group by Max date

Hi I have a resonably large table with the three fields shown below. Surveyor Name Unit Reference Date Completed Oliver 8088 08/09/2015 Chris 8088 28/09/2023 Rizwan 8088 08/09/2...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    So, sorry. I misread the requirement.

    See below code.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s/JLEstUtJRsjCwsABSRgaGproGlroGFkqxOtFKzhlFmcXIskbGIFkjiGxQZlV5Yh5OzcHJ+SUlQGFDU2NDQxO4dgtdY0OwvHdpTkpmAaoCkAEWugbmRCiIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Surveyor Name" = _t, #"Unit Reference" = _t, #"Date Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Surveyor Name", type text}, {"Unit Reference", Int64.Type}, {"Date Completed", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit Reference"}, {{"All", each Table.SelectRows(_, (x)=>x[Date Completed] = List.Max(_[Date Completed]))}})[[All]],
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Surveyor Name", "Unit Reference", "Date Completed"}, {"Surveyor Name", "Unit Reference", "Date Completed"})
in
    #"Expanded All"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
2 years ago

Insert the below step where #"Changed Type" is my previous step

= Table.Group(#"Changed Type", {"Surveyor Name", "Unit Reference"}, {{"Date Completed", each List.Max([Date Completed]), type nullable date}})

 

  • cottrera's avatar
    cottrera
    Post Prodigy
    2 years ago

    Hi thank you for responding so quickly. However the output is stillthe same os before
    Surveyor NameUnit ReferenceDate Completed

    Oliver Goldsmith808808/09/2015
    Rizwan Ahmed808808/09/2015
    Kuldip Jassal15311407/08/2015
    Scott Williams15311431/08/2023
    Chris Donczyk808828/09/2023

     Richard

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      2 years ago

      So, sorry. I misread the requirement.

      See below code.

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s/JLEstUtJRsjCwsABSRgaGproGlroGFkqxOtFKzhlFmcXIskbGIFkjiGxQZlV5Yh5OzcHJ+SUlQGFDU2NDQxO4dgtdY0OwvHdpTkpmAaoCkAEWugbmRCiIBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Surveyor Name" = _t, #"Unit Reference" = _t, #"Date Completed" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Surveyor Name", type text}, {"Unit Reference", Int64.Type}, {"Date Completed", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit Reference"}, {{"All", each Table.SelectRows(_, (x)=>x[Date Completed] = List.Max(_[Date Completed]))}})[[All]],
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Surveyor Name", "Unit Reference", "Date Completed"}, {"Surveyor Name", "Unit Reference", "Date Completed"})
in
    #"Expanded All"

       

      • cottrera's avatar
        cottrera
        Post Prodigy
        2 years ago

        That works fine thank you

    • Damian_CT_Nom's avatar
      Damian_CT_Nom
      Helper I
      2 years ago

      Maybe not as elegant but after that I would sort the date column descending and delete duplicates by Unit Reference so you only get the max date for Unit Reference and it's surveyor.

      • cottrera's avatar
        cottrera
        Post Prodigy
        2 years ago

        Hi 

         

        thanks for the tip.  I have tried this however whenremoving duplicates it removes the wrong ones and does not leave the max date.
        Richard