Hello all,
I have the following table :
| Group_ID | Start_Time | End_Time | Task_Name |
| 1 | 12:00:00 | 13:00:00 | Task1 |
| 1 | 13:00:00 | 14:15:00 | Task2 |
| 1 | 15:00:00 | 16:00:00 | Task3 |
| 2 | 09:00:00 | 10:30:00 | Task1 |
| 2 | 13:10:00 | 15:15:00 | Task2 |
I need to add rows on the following conditions :
- For each Group, check if there is any "free time" (if End_Time of current row = Start_Time of next row, then there is no free time).
- If there is "free time" between rows, you need to add a new row, where Start_Time = End_Time of previous row, End_Time = Start_Time of next row, and Task_Name = "Free".
Based on the example above, the expected result would be :
| Group_ID | Start_Time | End_Time | Task_Name |
| 1 | 12:00:00 | 13:00:00 | Task1 |
| 1 | 13:00:00 | 14:15:00 | Task2 |
| 1 | 14:15:00 | 15:00:00 | Free |
| 1 | 15:00:00 | 16:00:00 | Task3 |
| 2 | 09:00:00 | 10:30:00 | Task1 |
| 2 | 10:30:00 | 13:10:00 | Free |
| 2 | 13:10:00 | 15:15:00 | Task2 |
There are a lot more columns (and rows) in the actual dataset, but basically, these are the ones that matter for this case.
I have tried to use the "Generate Rows" tool, but can't seem to get the expected result. Does anyone have a similar case ?
