Forum Discussion
Find Text from List in Rows with same ID as current row
Hi, I could use some help finding a way to do the manipulation below in Power Query:
I want to create a custom column "Already_Approved" that shows
* "Yes" when chronologically² the last row in the same table with the same Document_ID has a transaction field that contains "Approving Done" or "Approved"
* and "No" when this chronologically last row does not have that Transaction.
Purpose: this table shows a history log of changes to the status of Documents, where each Document has a unique Document_ID. And I'd like to create a list of all non-approved Documents that are "Send for Approval" or "Send back for Editing". So after this step I will filter out only the "Send for Approval" and "Send back for editing" with "Already_Approved" = "No".
²Staying away from "chronologically" might create more robust results, though I don't want to overcomplicate my question 🙂 Cause for example "Document_ID=3" was send back after a mistake was found later on.
| Document_ID | Transaction | Date | Already_Approved |
| 1 | Created | 22/02/2023 11:00 | Yes |
| 1 | Send for Approval | 22/02/2023 11:01 | Yes |
| 1 | Approving Done | 22/02/2023 11:02 | Yes |
| 2 | Created | 22/02/2023 11:05 | No |
| 2 | Send for Approval | 22/02/2023 11:06 | No |
| 3 | Created | 22/02/2023 11:00 | No |
| 3 | Send for Approval | 22/02/2023 11:02 | No |
| 3 | Send back for Editing | 22/02/2023 11:03 | No |
| 3 | Send for Approval | 22/02/2023 11:05 | No |
| 3 | Approved | 22/02/2023 11:10 | No |
| 3 | Change Editing Required | 11/09/2023 09:11 | No |
This is only my second Power Query to date, so I'm a complete rookie. Thanks for the help!
NewStep=let a=Table.Group(PreviousStepName,"Document_ID",{"n",each Table.Sort(_,{"Date",1}){0}[Transaction]}) in Table.AddColumn(PreviousStepName,"Already_Approved",each if a{[Document_ID=[Document_ID]]}[n]="Approved" then "Yes" else "No")
2 Replies
NewStep=let a=Table.Group(PreviousStepName,"Document_ID",{"n",each Table.Sort(_,{"Date",1}){0}[Transaction]}) in Table.AddColumn(PreviousStepName,"Already_Approved",each if a{[Document_ID=[Document_ID]]}[n]="Approved" then "Yes" else "No")
Awesome Daniel! It made me swing my arms in the air for a winning gesture 😄
You made my day, I wish you an amazing weekend in return 😉
