Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
this is great - thankyou 🙂