Unpivot/transform data for use in Gantt Chart 2.2.3

[Nielf]

Hi,

I would like to use the Gantt 2.2.3 visual in a report. However, I am struggling to get the data transformed in the right way. 

To use the Gantt 2.2.3 I need to transform the data from a single row per task to three rows per task, like the example below:

I have played around with the unpivot function but haven't been able to find a solution. 

I hope you can help. 

[Nielf]

Thanks for the input, @nickvanmaele. I'll play around with it and see if I can make it work with my data. 

[nickvanmaele · Accepted solution]

hi @Nielf 

Perhaps first a remark about your input data. Being a project manager myself, if a milestone falls in the middle of a task, I usually create different tasks, one for the task preceding the milestone, and one for the task following the milestone. A milestone usually means that something is finished and another thing can then start.

In other words, you might want to consider changing your base data for Task 1 to something like this: 

Task	Start Date	End Date	Milestone?
Task 1a	2020-10-01	2020-11-04
Milestone 1	2020-11-04	2020-11-04	Yes
Task 1b	2020-11-04	2023-02-10
Milestone 2	2023-02-10	2023-02-10	Yes
Task 1c	2023-02-10	2023-10-16
Milestone 3	2023-10-16	2023-10-16	Yes

Now, to give a straight answer to your question, have a look at the Power Query code below:

(the Source step looks complicated simply because I used the "Enter Data" functionality)

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkkszlYwVNJRMjIwMtA1NNA1gHKMQRxDM7iMoa6BCUzGwAgoqRSrA9VuBBE30jWw1DU0giuyACKYDNAsY0Mk7UBbYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type date}, {"Column3", type date}, {"Column4", type date}, {"Column5", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Task"}, {"Column2", "Start Date"}, {"Column3", "End Date"}, {"Column4", "Milestone 1"}, {"Column5", "Milestone 2"}}),

//take a copy of the above data and process it some more to create a table with tasks that are not milestones
#"Original Data" = #"Renamed Columns",
#"Removed Columns1" = Table.RemoveColumns(#"Original Data",{"Milestone 1", "Milestone 2"}),
TableOfTasks = Table.AddColumn(#"Removed Columns1", "Milestone", each ""), //this is data that we will need later

//now continue to work on the data from the #"Renamed Columns" step to create a table with tasks that are milestones
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Task", "Start Date", "End Date"}, "Attribute", "Value"),
#"Replaced Start Date" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Start Date],each [Value],Replacer.ReplaceValue,{"Start Date"}),
#"Replaced End Date" = Table.ReplaceValue(#"Replaced Start Date",each [End Date],each [Value],Replacer.ReplaceValue,{"End Date"}),
#"Removed Columns" = Table.RemoveColumns(#"Replaced End Date",{"Value"}),
TableOfMilestones = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Milestone"}}),

//now append the tables with tasks and the table with milestones
#"Appended Query" = Table.Combine({TableOfTasks, TableOfMilestones}),

//and sort the whole thing to get the final table
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Task", Order.Ascending}, {"Start Date", Order.Ascending}})
in
#"Sorted Rows"

If this answer solves your problem, please indicate it as such.