Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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.
Proud to be a Super User! | |
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:
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.
Proud to be a Super User! | |