Forum Discussion
Set the current phase in a process
Hello everyone,
I need help creating a new column in Power Query that would define the "Current Phase" of each asset in my process.
Here is an example.
| ID | PhaseNumber | Start | End | ActivePhase | CurrentPhase |
| 001 | 1 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 001 | 2 | DD.MM.YYYY | *TODAY* | Yes | Yes |
| 001 | 3 | DD.MM.YYYY | *TODAY* | Yes | No |
| 001 | 4 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 002 | 1 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 002 | 2 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 002 | 3 | DD.MM.YYYY | *TODAY* | Yes | Yes |
| 002 | 4 | DD.MM.YYYY | *TODAY* | Yes | No |
| 003 | 1 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 003 | 2 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 003 | 3 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 003 | 4 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 004 | 1 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 004 | 2 | DD.MM.YYYY | DD.MM.YYYY | No | No |
| 004 | 3 | DD.MM.YYYY | *TODAY* | Yes | Yes |
| 004 | 4 | DD.MM.YYYY | DD.MM.YYYY | No | No |
Currently my "ActivePhase" column looks if the "End" date is *TODAY*, so "Yes", if not "No".
I can have several active phases but I can only have at most one "CurrentPhase".
So I would like my "Current Phase" column to take the active phase which has the smallest 'PhaseNumber' for each ID.
Do you have any ideas please.
Hi,
to obtain this
- add a Today column
Date.From( DateTimeZone.FixedUtcNow())
- determine ActivePhase with a conditional column
- filtered rows
- grouped filtered table
- from a previous step i get unfiltered table
- merge unfiltered with grouped
= Table.NestedJoin(Custom1, {"ID"}, MinStart, {"ID"}, "Custom1", JoinKind.LeftOuter)
(you can merge the unfiltered with itself then change the second table)
- expanded
- then add a conditional column for CurrentPhase
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
2 Replies
Hi,
to obtain this
- add a Today column
Date.From( DateTimeZone.FixedUtcNow())
- determine ActivePhase with a conditional column
- filtered rows
- grouped filtered table
- from a previous step i get unfiltered table
- merge unfiltered with grouped
= Table.NestedJoin(Custom1, {"ID"}, MinStart, {"ID"}, "Custom1", JoinKind.LeftOuter)
(you can merge the unfiltered with itself then change the second table)
- expanded
- then add a conditional column for CurrentPhase
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!
It's works.
Thanks for the quick answer.
