Get rows that have max value

[009co]

My query uses Group By to group by three categorical columns and get sum of a Price column. The resulting table looks like below.

I want to keep only the rows that have the max value in the Price column.

Purchase Order	Vendor	Customer	Price
1015	V03	99000043	6,000.00	<== KEEP
1016	V03	99000043	176,000.00	<== KEEP
1060	V02	99000047	3,351.70	<== EXCLUDE
1060	V02	99000039	18,648.30	<== KEEP
1086	V03	99000000	8,021.84	<== KEEP
1094	V03	99000169	401,000.00	<== KEEP
1102	V03	99000033	1,400.00	<== EXCLUDE
1102	V02	99000000	3,700.00	<== KEEP

I have tried List.Max() to get the desired result using:

= Table.SelectRows(Source, each ([Price] = List.Max(Source[Price])))

But when I run the query it takes very long time to complete, in fact I have not had it complete yet, I cancel it after 10 minutes. That is too long.

The data behind the query is csv file with about 20,000 rows x 35 columns text and numbers. So it isn't so big that it would be non-performant.

What other options are there besides List.Max() to get the desired results?

[Poohkrd · Accepted solution]

Hi, try this one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
    Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
    Grouped = Table.Group(Typed, {"Purchase Order"}, {{"recs", each Table.Max(_, " Price "), type record}}),
    Recs = Table.FromRecords( Grouped[recs] )
in
    Recs

You can also use List.Max, but with a small addition to the code. This edit is needed to bypass the lazy calculation of lists in the M language:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZFBC4MwDIX/ivQcSmJiGw+etjLGJuwyGDjxsN+w/79ax6ZSxEAheYeP9167zhBhGqoMmPZ8Gtp2wDLu3o+ycFyLNA7iPT6LGMXnG5FfTVNcQriZHmYoN0PxGkV+N8hh3pP/eWLgioAqZ/0CFR6H6/0Y9tC4/tJIwYmCClve9qX5gCnN5EsBS4qo2qpsompZozQ1OJkSpJ1dUUo0B2nS+f9/BJJFZbpa0spcQga/Yaz/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order" = _t, Vendor = _t, Customer = _t, #" Price " = _t, #" " = _t]),
    Typed = Table.TransformColumnTypes(Source, {{" Price ", type number}}, "en-US"),
    Grouped = Table.Group(Typed, {"Purchase Order"}, {{"tabs", each Table.SelectRows(_, let latest = List.Max([#" Price "]) in each [#" Price "] = latest), type table}}),
    Recs = Table.Combine( Grouped[tabs] )
in
    Recs