Forum Discussion
Filter only with most recent ID
Hi All,
Firstly Selva-Salimi thank you for your solution !
And Aikeno , According to your requirement, you want to show only the latest changed date in the power query, right?
We need to divide our Revison into two columns, MainID and SubRevison, with MainID in ascending order and SubRevison in descending order, so that we can find the most recent date of writing.
Then we create a new column to determine how many of the eligible rows are the latest date
if [SubRevision] = List.Max(Table.SelectRows(#"Renamed Columns", each [MainID] = _[MainID])[SubRevision])
then "Latest"
else "Old"
Finally you can optionally select latest to display the maximum date, based on this if you do not need the new custom auxiliary column you can also delete and will not affect the display of your maximum date.
If you still have questions you can check out the pbix file I uploaded, I hope it helps and I'd be honored if I could help you out!
Hope it helps!
Best regards,
Community Support Team_ Tom Shen
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
Thanks both for your help !
I downloaded the file but in your formula:
= Table.AddColumn(#"Renamed Columns", "Custom", each if [SubRevision] = List.Max(Table.SelectRows(#"Renamed Columns", each [MainID] = _[MainID])[SubRevision])
then "Latest"
else "Old")To which column refers the #"Renamed Columns" ?
Hi Aikeno ,
In response to your question, #“Renamed Columns” does not refer to a particular column, but rather to the name of the step in the previous operation, which represents the state of the table at the previous stage. Since there is no difference in the names of the columns after separating them according to the punctuation marks, we have performed the Renamed operation, which means that the step before add custom is #“Renamed Columns”, hope this helps you!
I am not sure to understand well your answer.
Here is what i tried and doesn't work.
