Forum Discussion

Aikeno's avatar
Aikeno
Icon for Helper I rankHelper I
1 year ago

Filter only with most recent ID

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 !

Need Help

6 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Icon for Solution Sage rankSolution Sage
    1 year ago

    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      Aikeno
      Icon for Helper I rankHelper I
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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!