Create new rows with information from previous and next rows

[MariusC]

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 ?

[caltang · Accepted solution]

You can consider this:

[MariusC]

Yes, thank you ! This should do ! 

I really don't get the use of the Generate Rows tool though. Might not be adapted to this specific scenario. 

But your answer is great !