How to keep rows with max value within another column value group

[JānisB]

I have a dataset

file	id	value
2023-07-31_wert.csv	555	45
2023-07-27_wert.csv	555	40
2023-07-15_wert.csv	555	35
2023-07-30_uiop.csv	444	35
2023-07-10_uiop.csv	444	30

I want to keep only rows with max date (or lates file, etc.) from file name within id

Result shoud be

file	id	value
2023-07-31_wert.csv	555	45
2023-07-30_uiop.csv	444	35

How can filter this in Power Query?

[G-newbie]

I had the same issue, but with different versions of a document and only wanted the line items from the latest version.

I grouped by maximum version number as below

Then expanded the new details column (Table).

This creates a duplicate line item for each version.

I found the original version column in the expanded table then added conditional column (I called mine Keep_Remove). If [MaxV] column = [Version_No] column then keep else remove. 

Filter out "remove" from the new column

Hope this helps.

Result:

[ToddChitt · Accepted solution]

So from your example for Id = 555, the winner (row to keep) is the first one because it has the max value, but you also want to include the details (like the file colum) from that row.

Question: Can you be sure that each ID will have ONE and ONLY ONE MAX(VALUE)?

If so, try this:

DUPLICATE the data set. 

On the duplicate, do a GROUP BY on Id column, and take an aggregate of MAX of the Value column.

Now JOIN this table with the original table. Do an INNER JOIN so that you only get matching records. Match on the ID and MAX(Value) = Value.