Filter only with most recent ID

[Aikeno]

Hello everyone,

I want to filter on a criteria ID by the most recent revision.

Exemple below, I have J5.11.a and J5.11.b and I want to keep only the one with the most recent revision here J5.11.b..

But if a new revision is created named J5.11.c, I want to only have this one and no more J5.11.a and J5.11.b

I have no idea how I can manage to have this result.

If you have some idea on that !

Thank for you help in advance !

[Anonymous]

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.

[Aikeno]

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" ?

[Anonymous]

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!

[Aikeno]

I am not sure to understand well your answer.

Here is what i tried and doesn't work.

[Anonymous]

Hi @Aikeno ,
Can you provide some sample data or related pbix files, this will be more conducive to our analysis of your problem, in order to help you improve the problem faster, look forward to your reply!

[Selva-Salimi]

Hi @Aikeno 

you can write a column in your table as follows:

 if (max('Table'[version])='Table'[version] , 1 ,0)

and then simply add this column to visual filter pane and set it to 1.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.