Let's say I have below table. I'm wondering if there is a way in Power Query to fix this planning.
The order of processes should be first by priority, and then by date.
As you can see, there is a problem on 11-1-2021 to 15-1-2021, where the resource usage is 175%.
I would like to move al processes forward in time that are conflicting, while respecting the desired order of the processes. Obviously processes of 25% and 75% could run on the same day.
Below things come to my mind that could be part of the solution, but I don't know how to make cake of it.
The order of processes should be first by priority, and then by date.
As you can see, there is a problem on 11-1-2021 to 15-1-2021, where the resource usage is 175%.
I would like to move al processes forward in time that are conflicting, while respecting the desired order of the processes. Obviously processes of 25% and 75% could run on the same day.
Below things come to my mind that could be part of the solution, but I don't know how to make cake of it.
- Group by Date and sum Resource usage shows me the conflicts.
- Merging with a date table and calculating resource availability for each day.
- Using a recursive function?
- Using a loop?
| Process | Date | Resource usage | Priority |
|---|---|---|---|
| A | 1-1-2021 | 100,00% | 3 |
| A | 2-1-2021 | 100,00% | 3 |
| A | 3-1-2021 | 100,00% | 3 |
| A | 4-1-2021 | 100,00% | 3 |
| A | 5-1-2021 | 100,00% | 3 |
| B | 6-1-2021 | 0,00% | 3 |
| B | 7-1-2021 | 0,00% | 3 |
| B | 8-1-2021 | 0,00% | 3 |
| B | 9-1-2021 | 0,00% | 3 |
| B | 10-1-2021 | 0,00% | 3 |
| C | 11-1-2021 | 75,00% | 3 |
| C | 12-1-2021 | 75,00% | 3 |
| C | 13-1-2021 | 75,00% | 3 |
| C | 14-1-2021 | 75,00% | 3 |
| C | 15-1-2021 | 75,00% | 3 |
| D | 16-1-2021 | 0,00% | 3 |
| D | 17-1-2021 | 0,00% | 3 |
| D | 18-1-2021 | 0,00% | 3 |
| D | 19-1-2021 | 0,00% | 3 |
| D | 20-1-2021 | 0,00% | 3 |
| E | 21-1-2021 | 25,00% | 3 |
| E | 22-1-2021 | 25,00% | 3 |
| E | 23-1-2021 | 25,00% | 3 |
| E | 24-1-2021 | 25,00% | 3 |
| E | 25-1-2021 | 25,00% | 3 |
| F | 6-1-2021 | 100,00% | 2 |
| F | 7-1-2021 | 100,00% | 2 |
| F | 8-1-2021 | 100,00% | 2 |
| F | 9-1-2021 | 100,00% | 2 |
| F | 10-1-2021 | 100,00% | 2 |
| G | 11-1-2021 | 0,00% | 2 |
| G | 12-1-2021 | 0,00% | 2 |
| G | 13-1-2021 | 0,00% | 2 |
| G | 14-1-2021 | 0,00% | 2 |
| G | 15-1-2021 | 0,00% | 2 |
| H | 16-1-2021 | 75,00% | 2 |
| H | 17-1-2021 | 75,00% | 2 |
| H | 18-1-2021 | 75,00% | 2 |
| H | 19-1-2021 | 75,00% | 2 |
| H | 20-1-2021 | 75,00% | 2 |
| I | 21-1-2021 | 0,00% | 2 |
| I | 22-1-2021 | 0,00% | 2 |
| I | 23-1-2021 | 0,00% | 2 |
| I | 24-1-2021 | 0,00% | 2 |
| I | 25-1-2021 | 0,00% | 2 |
| J | 26-1-2021 | 25,00% | 2 |
| J | 27-1-2021 | 25,00% | 2 |
| J | 28-1-2021 | 25,00% | 2 |
| J | 29-1-2021 | 25,00% | 2 |
| J | 30-1-2021 | 25,00% | 2 |
| K | 11-1-2021 | 100,00% | 1 |
| K | 12-1-2021 | 100,00% | 1 |
| K | 13-1-2021 | 100,00% | 1 |
| K | 14-1-2021 | 100,00% | 1 |
| K | 15-1-2021 | 100,00% | 1 |
| L | 16-1-2021 | 0,00% | 1 |
| L | 17-1-2021 | 0,00% | 1 |
| L | 18-1-2021 | 0,00% | 1 |
| L | 19-1-2021 | 0,00% | 1 |
| L | 20-1-2021 | 0,00% | 1 |
| M | 21-1-2021 | 75,00% | 1 |
| M | 22-1-2021 | 75,00% | 1 |
| M | 23-1-2021 | 75,00% | 1 |
| M | 24-1-2021 | 75,00% | 1 |
| M | 25-1-2021 | 75,00% | 1 |
| N | 26-1-2021 | 0,00% | 1 |
| N | 27-1-2021 | 0,00% | 1 |
| N | 28-1-2021 | 0,00% | 1 |
| N | 29-1-2021 | 0,00% | 1 |
| N | 30-1-2021 | 0,00% | 1 |
| O | 31-1-2021 | 25,00% | 1 |
| O | 1-2-2021 | 25,00% | 1 |
| O | 2-2-2021 | 25,00% | 1 |
| O | 3-2-2021 | 25,00% | 1 |
| O | 4-2-2021 | 25,00% | 1 |
