Transforming Data for use in Gantt Chart

[Anonymous]

I'm trying to create a gantt chart using the Microsoft Gantt Chart visualization.  But I'm having difficulty getting the data formatted to do it.  I need to format the data so i can all of the tasks to be on one row for each project, which I believe means I need all of the start/finish dates to be in two columns, but I can't seem to transform the data correctly to do it.

The end users want the start/finish for every task to be on one row for each project

.

Here is an example of my source data, although there are actually around 20 tasks (note: this isn't the actual data due to it being sensitive):

Project	Task 1 Start	Task 1 Finish	Task 2 Start	Task 2 Finish	Task 3 Start	Task 3 Finish	Task 4 Start	Task 4 Finish
Name 1	1/1/2021	6/1/2021	2/1/2021		3/1/2022	4/1/2022	1/15/2021
Name 2	3/1/2021	5/1/2021	1/1/2021	3/1/2021	3/1/2021		3/1/2021	9/1/2021
Name 3	5/1/2021		7/1/2021	9/1/2021			9/1/2021
Name 4	7/1/2021	9/1/2021	5/1/2021	7/1/2021	5/1/2021	7/1/2021	7/1/2021	9/1/2021

I need to get it into this format for the gantt chart:

Project	Task	Start	Finish
Name 1	Task 1	1/1/2021	6/1/2021
Name 2	Task 1	3/1/2021	5/1/2021
Name 3	Task 1	5/1/2021
Name 4	Task 1	7/1/2021	9/1/2021
Name 1	Task 2	2/1/2021
Name 2	Task 2		3/1/2021
Name 3	Task 2
Name 4	Task 2	5/1/2021	7/1/2021
Name 1	Task 3	3/1/2022	4/1/2022
Name 2	Task 3	3/1/2021
Name 3	Task 3
Name 4	Task 3	5/1/2021	7/1/2021
Name 1	Task 4	1/15/2021
Name 2	Task 4	3/1/2021	9/1/2021
Name 3	Task 4	9/1/2021
Name 4	Task 4	7/1/2021	9/1/2021

I've tried unpivot and pivot to get it into this format, but I it doesn't seem to work properly.  This is what I end up getting (the extra blank rows for the "start" and "Finish" are causing problems witht he gantt chart:

Project	Task	Start	Finish
Name 1	Task 1	1/1/2021
Name 2	Task 1	3/1/2021
Name 3	Task 1	5/1/2021
Name 4	Task 1	7/1/2021
Name 1	Task 1		6/1/2021
Name 2	Task 1		5/1/2021
Name 3	Task 1
Name 4	Task 1		9/1/2021
Name 1	Task 2	2/1/2021
Name 2	Task 2
Name 3	Task 2
Name 4	Task 2	5/1/2021
Name 1	Task 2
Name 2	Task 2		3/1/2021
Name 3	Task 2
Name 4	Task 2		7/1/2021
Name 1	Task 3	3/1/2022
Name 2	Task 3	3/1/2021
Name 3	Task 3
Name 4	Task 3	5/1/2021
Name 1	Task 3		4/1/2022
Name 2	Task 3
Name 3	Task 3
Name 4	Task 3		7/1/2021

Any advice on how to transform the source data so all the start and finish dates are in two columns for each Project/task

Thanks.

[philouduv · Accepted solution]

Hey @Anonymous ,

First of all go in query mode.

Then your fist operation is to merge each start and finish task ( task_1 start merge with task_1 end) which will transform your :

Into : 

(I chose to merge with a semi-colon delimiter)

Then Select only the task column and unpivot these columns giving you :

And the last step You have to do is just to split column attribute to have start date and end date (and possibly change the type of data to get the "date" format / rename some columns :)).

Hope I could help

Best regards,

Ps: If You want to match perfectly with the output you asked, just sort ascending the column with the tasks

[Anonymous]

Worked like a charm!  Thanks!