Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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
Worked like a charm! Thanks!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |