Get rows that have max value

Question

My query uses Group By to group by three categorical columns and get sum of a Price column. The resulting table looks like below.&nbsp;I want to keep only the rows that have the max value in the Price column.&nbsp;Purchase Order&nbsp; &nbsp;&nbsp;Vendor&nbsp; &nbsp;&nbsp;Customer&nbsp; &nbsp;&nbsp;&nbsp;Price&nbsp;&nbsp;1015&nbsp; &nbsp;&nbsp;V03&nbsp; &nbsp;&nbsp;99000043&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6,000.00&nbsp; &nbsp;&nbsp;&lt;== KEEP1016&nbsp; &nbsp;&nbsp;V03&nbsp; &nbsp;&nbsp;99000043&nbsp; &nbsp;&nbsp;&nbsp; 176,000.00&nbsp; &nbsp;&nbsp;&lt;== KEEP1060&nbsp; &nbsp;&nbsp;V02&nbsp; &nbsp;&nbsp;99000047&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,351.70&nbsp; &nbsp;&nbsp;&lt;== EXCLUDE1060&nbsp; &nbsp;&nbsp;V02&nbsp; &nbsp;&nbsp;99000039&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 18,648.30&nbsp; &nbsp;&nbsp;&lt;== KEEP1086&nbsp; &nbsp;&nbsp;V03&nbsp; &nbsp;&nbsp;99000000&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 8,021.84&nbsp; &nbsp;&nbsp;&lt;== KEEP1094&nbsp; &nbsp;&nbsp;V03&nbsp; &nbsp;&nbsp;99000169&nbsp; &nbsp;&nbsp;&nbsp; 401,000.00&nbsp; &nbsp;&nbsp;&lt;== KEEP1102&nbsp; &nbsp;&nbsp;V03&nbsp; &nbsp;&nbsp;99000033&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1,400.00&nbsp; &nbsp;&nbsp;&lt;== EXCLUDE1102V0299000000&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,700.00&lt;== KEEP&nbsp;I have tried List.Max() to get the desired result using:&nbsp;= Table.SelectRows(Source, each ([Price] = List.Max(Source[Price])))&nbsp;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.&nbsp;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.&nbsp;What other options are there besides&nbsp;List.Max() to get the desired results?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

poohkrd · Accepted Answer

Hi, try this one:&nbsp;&nbsp;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&nbsp;You can also use List.Max, but with&nbsp;a small addition to the code. This edit is needed to bypass the lazy calculation of lists in the M language:&nbsp;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&nbsp;&nbsp;

Forum Discussion

Get rows that have max value

1 Reply

Related content

remove duplicate value within a row

Row Value Formatting

Matrix Row Totals within Row Values

Previous Row Value Dax

Concatenate rows values

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