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 | 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
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
4 Replies
- mahoneypat
Microsoft Employee
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
Sorry I alsow found this post. Which does the trick
olimiloPost Prodigy
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)?
marc_freiHelper I
I'm wondering which way is less cpu/ram consuming with a lot of data?