Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Transforming Data for use in Gantt Chart

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):

 

ProjectTask 1 StartTask 1 FinishTask 2 StartTask 2 FinishTask 3 StartTask 3 FinishTask 4 StartTask 4 Finish
Name 11/1/20216/1/20212/1/2021 3/1/20224/1/20221/15/2021 
Name 23/1/20215/1/20211/1/20213/1/20213/1/2021 3/1/20219/1/2021
Name 35/1/2021 7/1/20219/1/2021  9/1/2021 
Name 47/1/20219/1/20215/1/20217/1/20215/1/20217/1/20217/1/2021

9/1/2021

 

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

Project  Task  StartFinish
Name 1Task 11/1/20216/1/2021
Name 2Task 13/1/20215/1/2021
Name 3Task 15/1/2021 
Name 4Task 17/1/20219/1/2021
Name 1Task 22/1/2021 
Name 2Task 2 3/1/2021
Name 3Task 2  
Name 4Task 25/1/20217/1/2021
Name 1Task 33/1/20224/1/2022
Name 2Task 33/1/2021 
Name 3Task 3  
Name 4Task 35/1/20217/1/2021
Name 1Task 41/15/2021 
Name 2Task 43/1/20219/1/2021
Name 3Task 49/1/2021 
Name 4Task 47/1/20219/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:

ProjectTaskStartFinish
Name 1Task 11/1/2021 
Name 2Task 13/1/2021 
Name 3Task 15/1/2021 
Name 4Task 17/1/2021 
Name 1Task 1 6/1/2021
Name 2Task 1 5/1/2021
Name 3Task 1  
Name 4Task 1 9/1/2021
Name 1Task 22/1/2021 
Name 2Task 2  
Name 3Task 2  
Name 4Task 25/1/2021 
Name 1Task 2  
Name 2Task 2 3/1/2021
Name 3Task 2  
Name 4Task 2 7/1/2021
Name 1Task 33/1/2022 
Name 2Task 33/1/2021 
Name 3Task 3  
Name 4Task 35/1/2021 
Name 1Task 3 4/1/2022
Name 2Task 3  
Name 3Task 3  
Name 4Task 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.

1 ACCEPTED SOLUTION
philouduv
Resolver III
Resolver III

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 :

philouduv_0-1650381537518.png

Into : 

philouduv_1-1650381609837.png

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

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

philouduv_2-1650381727507.png


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 :)).

philouduv_3-1650381937642.png

 


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

View solution in original post

2 REPLIES 2
philouduv
Resolver III
Resolver III

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 :

philouduv_0-1650381537518.png

Into : 

philouduv_1-1650381609837.png

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

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

philouduv_2-1650381727507.png


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 :)).

philouduv_3-1650381937642.png

 


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
Not applicable

Worked like a charm!  Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.