Forum Discussion
How to keep rows with max value within another column value group
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?
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.
2 Replies
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.
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:
