renaming all but latest value in a column for an ID in another column.

[thisguy]

i have three columns - status, user and month.   the status is the same for every month for each user but it is only correct for the last month, all earlier entries should be "Active".  is there a way in power query to change?

how it is:

Status	User	Month
Complete	User1	1/01/2021
Complete	User1	1/02/2021
Complete	User1	1/03/2021
Withdrawn	User2	1/01/2021
Withdrawn	User2	1/02/2021
Withdrawn	User2	1/03/2021
Active	User2	1/01/2021
Active	User2	1/02/2021
Active	User2	1/03/2021

how it should be:

Status	User	Month
Active	user1	1/01/2021
Active	User1	1/02/2021
Complete	user1	1/03/2021
Active	User2	1/01/2021
Active	User2	1/02/2021
Withdrawn	User2	1/03/2021
Active	User2	1/01/2021
Active	User2	1/02/2021
Active	User2	1/03/2021

[Fowmy · Accepted solution]

@thisguy 

You can add a custom column in PQ as follows. Please check the attached file below my signature.

(x)=>  if List.Max(Table.SelectRows( #"Changed Type", each x[User]=[User] and x[Status]=[Status])[Month]) = x[Month] then x[Status] else "Active"

[thisguy]

this is great - thankyou 🙂