Selecting active records from a table

[RobDueck]

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?

[RobDueck]

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]

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!

[isjoycewang]

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: