Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power query group rows and only keep row with highest value

Hi People,   My table looks as follows.    Name Number Value John 1 100 John 2 200 Bert 1 150   My desired output is Name Number Value John 2 200 Bert 1 1...
Need Help
Show and Tell
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Here is one way to do it.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that the Table.Buffer is needed to maintain the desired descending sort order.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTIEYQMDpVgduJARCEOFnFKLSmCqTIFCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Number", Int64.Type}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Number", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Name"})
in
    #"Removed Duplicates"

     

    Pat

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Here is one way to do it.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.  Note that the Table.Buffer is needed to maintain the desired descending sort order.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFPSUTIEYQMDpVgduJARCEOFnFKLSmCqTIFCsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Number = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Number", Int64.Type}, {"Value", Int64.Type}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"Number", Order.Descending}})),
    #"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"Name"})
in
    #"Removed Duplicates"

 

Pat

 

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
2 months ago

Sorry to necro this 5-year old thread, but would the Buffer and Remove Duplicate sequence of steps mean that the Remove Duplicate step will follow the order as is was saved by the Table.Buffer function? I might be wrong but has it been established how Remove Duplicates work with regard to the order of the data as it is loaded (or shown in the UI)?