Unpivot/transform data for use in Gantt Chart 2.2.3

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: 

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.