Forum Discussion
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...
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
Microsoft Employee
Microsoft EmployeeHere 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
Sorry I alsow found this post. Which does the trick