Forum Discussion

RobDueck's avatar
RobDueck
New Member
2 years ago

Selecting active records from a table

I have a table of data that contains  one or more records for a particular item.  Only one record is the current active record and this has to be determined based on the values in several date fields.  I am looking for the record with the most recent (in the past) effective date, a next change date in the future and a termination date that is either in the future or null in order to get the current active record and the associated data in some other fields like Description/Title.  Is there a way to transform this so that the data table for my report only includes one record for each item?

Need Help

3 Replies

  • isjoycewang's avatar
    isjoycewang
    Solution Supplier
    2 years ago

    Hi RobDueck ,

     

    I create some dummy data for demo: Exercise File

    You can open the editor and follow the steps there, and you can also modify some filter steps to better fit in your case. Thanks!

     

    StartPoint:

    EndPoint:

     

  • RobDueck's avatar
    RobDueck
    New Member
    2 years ago

    Thanks.  That helps.  I haven't used Power Query and Power BI enough yet to get my head out of the Access "write a query with multiple criteria" mentality.

    • isjoycewang's avatar
      isjoycewang
      Solution Supplier
      2 years ago

      Hi RobDueck ,

       

      I think "Filter Rows" may be fine to grouped in one query, but ranking in category is tricky in Power Query so we take more steps to get it.

       

      Does this solve your question?
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Thanks!