Forum Discussion
Looking to match value from one colu
Hello PowerBI Experts,
I am a learner and have been stepping into new challenges each day.
I am hoping you will be able to steer me in the right direction.
I have candidates in each order and need to identify and list the Highest Application Status (last column in the data sample) they have attained in that order.
I identified the highest stage a candidate attained (in Column E). Perhaps that step is not needed.
Now, I think I would need a fomula or some type of lookup function to identify the Application Status (C) that corresponds to the highest stage attained (Column D). Column E already has highest stage attained by that candidate so might be easier to use.
Something like: identify highest stage for candiate A in order ID, pull application status name from Column C corresponding to highest stage into Highest Application Status (F).
More context:
Not all candiates go to the same number of stages, some statuses could be skipped and candidates could go back but order of stages is always from 1 to 2 to 3, etc. per candiate. Some end at stage 2, some go to stage 10.
I only need to identify the highest stage each candiate has attained in each order.
Order numbers are unique
Candidates could belong to more than one order but all I need is the highest stage they attained in each order.
| Order ID | Candidate name | Application Status | Stage | Highest Stage for candidate | Higest Application Status |
| 1 | Amba | New | 1 | 6 | |
| 1 | Amba | Offer Accepted | 6 | 6 | Offer Accepted |
| 1 | Amba | Offer Cancelled | 3 | 6 | |
| 1 | Amba | Offer Pending Approval | 4 | 6 | |
| 1 | Amba | Offer presented | 2 | 6 | |
| 1 | Amba | Offer Sent | 5 | 6 | |
| 1 | Henriette | New | 1 | 1 | New |
| 1 | Jaspreet | Declined | 3 | 3 | Declined |
| 1 | Jaspreet | New | 1 | 3 | |
| 1 | Jaspreet | Offered | 2 | 3 | |
| 1 | Krista | New | 1 | 4 | |
| 1 | Krista | Offer Pending Approval | 4 | 4 | Offer Pending Approval |
| 1 | Krista | Pre-Screen | 2 | 4 | |
| 1 | Krista | Presented to HM | 3 | 4 | |
| 1 | Sorus | New | 1 | 1 | New |
| 6 | Claudia | Declined | 2 | 2 | Declined |
| 6 | Claudia | New | 1 | 2 | |
| 6 | Madison | New | 1 | 2 | |
| 6 | Madison | Offered | 2 | 2 | Offered |
| 6 | Nadia | Declined | 3 | 3 | Declined |
| 6 | Nadia | New | 1 | 3 | |
| 6 | Nadia | Rejected by TAP | 2 | 3 | |
| 9 | Rupinder | New | 1 | 1 | New |
| 9 | Sukhman | New | 1 | 2 | |
| 9 | Sukhman | Pre-Screen | 2 | 2 | Pre-screen |
| 9 | Victoria | Declined | 2 | 3 | |
| 9 | Victoria | Declined | 3 | 3 | Declined |
| 9 | Victoria | New | 1 | 3 | |
| 12 | ADEL | Declined | 3 | 3 | Declined |
| 12 | ADEL | In-Progress | 2 | 3 | |
| 12 | ADEL | New | 1 | 3 | |
| 12 | ALIREZA | Declined | 3 | 3 | Declined |
| 12 | ALIREZA | In-Progress | 2 | 3 | |
| 12 | ALIREZA | New | 1 | 3 | |
| 12 | ASSEM | Declined | 3 | 3 | Declined |
| 12 | ASSEM | In-Progress | 2 | 3 | |
| 12 | ASSEM | New | 1 | 3 | |
| 12 | DIANE | New | 1 | 3 | |
| 12 | DIANE | Offer presented to candidate | 2 | 3 | |
| 12 | DIANE | Offered | 3 | 3 | Offered |
| 12 | Iman | Declined | 3 | 3 | Declined |
| 12 | Iman | In-Progress | 2 | 3 | |
| 12 | Iman | New | 1 | 3 | |
| 12 | Jila | Declined | 3 | 5 | |
| 12 | Jila | Declined | 5 | 5 | Declined |
| 12 | Jila | New | 1 | 5 | |
| 12 | Jila | Offered | 4 | 5 | |
| 12 | Jila | Rejected by TAP | 2 | 5 | |
| 12 | Jupinder | Discussion on Hold | 2 | 2 | Discussion on Hold |
| 12 | Jupinder | New | 1 | 2 | |
| 12 | Meaghan | Declined | 4 | 4 | Declined |
| 12 | Meaghan | In-Progress | 2 | 4 | |
| 12 | Meaghan | New | 1 | 4 | |
| 12 | Meaghan | Offer presented to candidate | 3 | 4 | |
| 12 | Michael | New | 1 | 2 | |
| 12 | Michael | Pre-Screen | 2 | 2 | Pre-screen |
| 12 | Muhammad | Declined | 2 | 2 | Decined |
| 12 | Muhammad | New | 1 | 2 | |
| 12 | MUHAMMAD | Declined | 3 | 3 | Declined |
| 12 | MUHAMMAD | New | 1 | 3 | |
| 12 | MUHAMMAD | Rejected by TAP | 2 | 3 | |
| 12 | Peter | Declined | 3 | 3 | Declined |
| 12 | Peter | New | 1 | 3 | |
| 12 | Peter | Rejected by TAP | 2 | 3 | |
| 12 | Pushpinder | Declined | 3 | 3 | Declined |
| 12 | Pushpinder | In-Progress | 2 | 3 | |
| 12 | Pushpinder | New | 1 | 3 | |
| 12 | Tarek | Declined | 3 | 3 | Declined |
| 12 | Tarek | New | 1 | 3 | |
| 12 | Tarek | Rejected by TAP | 2 | 3 |
Thank you,
Renee
NewStep=Table.Combine(Table.Group(PreviousStepName,"Order ID",{"n",let a=List.Max([Stage]) in Table.AddColumn(_,"Highest Application Status",each if [Stage]=a then [Application Status] else null)})[n])
3 Replies
- wdx223_Daniel
Community Champion
NewStep=Table.Combine(Table.Group(PreviousStepName,"Order ID",{"n",let a=List.Max([Stage]) in Table.AddColumn(_,"Highest Application Status",each if [Stage]=a then [Application Status] else null)})[n])
