Filter out all rows other than the ones with a max value for the corresponding ID

[vha_labo]

Trying to determine how to return only the rows where the value for that ID number is a maximum.  Example:

Row	ID	Value
1	1	3
2	1	7
3	1	5
4	2	21
5	2	12
6	3	17
7	3	9
8	3	3
9	3	32
10	4	4
11	4	9

In this case, I'd be looking for only rows 2, 4, 9, and 11 to remain when the query is applied.  Any thoughts on how to accomplish would be appreciated.  Thanks.

[edhans · Accepted solution]

Use this forumula:

let 
     varID = [ID],
     varValue = [Value]
in 
Table.Max(
    Table.SelectRows(#"Changed Type", each [ID] = varID),
    "Value"
)[Value] = varValue

It will return this table:

Then just filter on the true/false to keep what you want. Full M code below:

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc3BDcAwCAPAXXjnUZOkaWZB2X+NUuM+kHxChgiDNU6308JcWlSXJjUyfXsHOUU4efNGsqpL3NQj1ZP9q5q4Mg8OCTGr5wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, ID = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", Int64.Type}, {"ID", Int64.Type}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
     varID = [ID],
     varValue = [Value]
in 
Table.Max(
    Table.SelectRows(#"Changed Type", each [ID] = varID),
    "Value"
)[Value] = varValue)
in
    #"Added Custom"

[vha_labo]

With a little tweaking to the process of getting it there, this worked.  Thanks!

[edhans]

Glad I was able to help @vha_labo