Power query group rows and only keep row with highest value

[Anonymous]

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	150

So per name I only want to keep the row with the highest number for that name. 

Can you tell me how to achieve this

[marc_frei]

I'm wondering which way is less cpu/ram consuming with a lot of data?

[mahoneypat · Accepted solution]

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

[Anonymous]

Sorry I alsow found this post. Which does the trick

https://community.powerbi.com/t5/Power-Query/Return-only-rows-with-max-value-from-specific-column/m-...