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 NameUnit ReferenceDate Completed
Oliver808808/09/2015
Chris808828/09/2023
Rizwan808808/09/2015
Scott15311431/08/2023
Kuldip15311407/08/2015
Kuldip15311407/08/2015


I require some help to how I can group the Unit Reference to display the Max Completion Date and who the Surveyor was.
I would expect this result

Surveyor NameUnit ReferenceDate Completed
Chris808828/09/2023
Scott15311431/08/2023


Please note I already have some M-Code steps being performed on this table.
thankyou
Richard

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"

     

8 Replies

  • 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"