Prepare data for Gantt visual

[Anonymous]

I'm trying to prepare my data to visualise in a gantt schema but I can't figure out how to transform it correctly. 

The data is structured so that i have one row per ID, and then multiple columns for each "step" that my id is in. 

and to make it complicated each ID can be in each step multiple times, and this is shown as a delimted comma in the row. 

Example:

ID	Step 1	Step 2	Step 3	Step 4	Step 5	Step 6	Step 7	Step 8
1	2024-04-02 17:21	2024-04-02 17:21,2024-06-10 01:21	2024-06-09 22:47,2024-09-25 18:06		2024-09-27 09:32,2024-10-17 09:50	2024-10-10 08:56,2024-10-22 09:07	2024-10-11 11:08
2	2023-11-15 14:37	2023-11-15 14:37	2024-02-20 10:56,2024-06-12 16:38,2024-09-18 15:42		2024-02-22 13:21,2024-04-23 12:57,2024-06-04 15:05,2024-06-18 09:05,2024-07-16 13:36,2024-08-12 15:03,2024-08-15 08:52,2024-10-16 13:22	2024-03-25 09:21,2024-05-02 09:11,2024-07-16 12:55,2024-07-23 15:34,2024-08-14 13:11,2024-08-23 09:00,2024-10-21 09:31	2024-09-18 15:17,2024-09-18 16:52,2024-10-16 14:59
3	2024-03-08 11:54	2024-03-08 11:54	2024-11-12 09:11	2024-11-07 12:07	2024-11-12 09:11,2024-11-18 15:42	2024-11-14 17:49,2024-11-27 14:31	2024-11-14 17:13

I've tried unpivoting the table, and then split into rows, but I'm unsure how to get the proper start and end dates in the correct order, so that they don't overlap

[Anonymous]

Hi, @Anonymous 

May I ask which columns can be used as start date and end date and what is the dilemma you are facing, you can describe it more clearly with pictures or words. Thanks for your understanding.

Best Regards,
Yang
Community Support Team

[Anonymous]

@Anonymous  Thanks for the reply!

Start date is always the earliest date, no matter the column, but should according to business logic be step 1. 

Then the next step should be whatever date comes next, this date would then also be the end date for the previous row. 

So if it goes from step 2 to  step 3 2024-01-01, then that date would be end date for step 2, and start date for step 3

I tried sorting then grouping and adding an index, but then I probably messed up somehow with the index and duplicating columns etc, so maybe there's another solution there or how to do it properly 

Edit: 

I'm trying to merge all the columns into first one column, that should be sorted/grouped per ID, and the dates in order. Then I want to create a second table with the same dates, but shifted one step. So that the End date is the next row's start date. 

[Anonymous · Accepted solution]

@Anonymous 

I've managed to group and index per id, so that I per ID, get all the dates in a sorted id with an indexed count. And then starts over again per next ID. Which is perfect,

My issue now is that I want to duplicate my date/timestamp column, but shifted one step so that the date/timestamp is one step behind the other one. So that the end date is the start date of the next row. 

Edit: Solved with calculated column:

End Timestamp =
CALCULATE(
MIN('Table'[Start Timestamp]),
FILTER(
'Table',
'Table'[Key] = EARLIER('Table'[Key]) &&
'Table'[index] = EARLIER('Table'[index]) + 1
)
)