Transpose Columns for Gantt 2.2.3

[GCH_Ryan]

I have a table that is currently set-up like this: 

Project	Project Manager	Task 1 Start	Task 1 End	Task 2 Start	Milestone 1	Milestone 2	Task 2 End	Task 3 Start	Milestone 3	Task 3 End
Project 1	PM 1	3/1/2020	3/5/2020	3/6/2020	3/7/2020	3/9/2020	3/10/2020	3/11/2020	3/15/2020	3/20/2020
Project 2	PM 2
Project 3	PM1
Project 4	PM 3

And I would like to transform the table so I can more easily utilize it in Gantt 2.2.3, which I believe means it needs to be set up like this:

Project	Project Manager	Event	Start	End	Milestone
Project 1	PM 1	Task 1	3/1/2020	3/5/2020
Project 1	PM 1	Task 2	3/6/2020	3/10/2020
Project 1	PM 1	Milestone 1			3/7/2020
Project 1	PM 1	Milestone 2			3/9/2020
Project 1	PM 1	Task 3	3/11/2020	3/20/2020
Project 1	PM 1	Milestone 3			3/15/20202
Project 2	PM 2	Task 1
Project 2	PM 2	Task 2
Project 2	PM 2	Milestone 1
Project 2	PM 2	Milestone 2
Project 2	PM 2	Task 3
Project 2	PM 2	Milestone 3
Project 3	PM1	Task 1
Project 3	PM1	Task 2
Project 3	PM1	Milestone 1
Project 3	PM1	Milestone 2
Project 3	PM1	Task 3
Project 3	PM1	Milestone 3
Project 4	PM 3	Task 1
Project 4	PM 3	Task 2
Project 4	PM 3	Milestone 1
Project 4	PM 3	Milestone 2
Project 4	PM 3	Task 3
Project 4	PM 3	Milestone 3

I think I need to transpose the headers and make a conditional column to place the start / end / milestone dates into their new columns but I'm not sure about the proper method for doing so. Thank you and stay healthy out there! 

[camargos88 · Accepted solution]

Hi @GCH_Ryan 

Try this code on Advanced Editor (I assumed you are using TAB as separator):

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlHSgbEUfBPzEtNTi4AiIYnF2QqGCsEliUUlCK5rXgqMYwSRUwDyfTNzUotL8vNSFQxReEYItUgajeGGIlQaIyRBKmN14I4DGxngC6aM9Q31jQyMDMBMUwTTDME0RzAtEUxDAyQ2khmGSIYYQdUg220EsRtEKRDEyBqNFcA6DQnrQtdpArESFCA4UWwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project", type text}, {"Project Manager", type text}, {"Task 1 Start", type date}, {"Task 1 End", type date}, {"Task 2 Start ", type date}, {"Milestone 1", type date}, {"Milestone 2", type date}, {"Task 2 End", type date}, {"Task 3 Start", type date}, {"Milestone 3", type text}, {"Task 3 End", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Project", "Project Manager"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Label", each if Text.Contains([Attribute], "Start") then "Start"
else if Text.Contains([Attribute], "End") then "End" else "Milestone"),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Label]), "Label", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","Start","",Replacer.ReplaceText,{"Attribute"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","End","",Replacer.ReplaceText,{"Attribute"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value1",{{"Project", Text.Trim, type text}, {"Project Manager", Text.Trim, type text}, {"Attribute", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Project", Text.Clean, type text}, {"Project Manager", Text.Clean, type text}, {"Attribute", Text.Clean, type text}}),
#"Grouped Rows" = Table.Group(#"Cleaned Text", {"Project", "Project Manager", "Attribute"}, {{"Start", each List.Max([Start]), type date}, {"End", each List.Max([End]), type anynonnull}, {"Milestone", each List.Max([Milestone]), type anynonnull}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Grouped Rows", {{"Start", type date}, {"End", type date}, {"Milestone", type date}}, "en-US")
in
#"Changed Type with Locale"

I hope it can help you,

Ricardo

[amitchandak]

@ImkeF , can you help?

[ImkeF]

Hi @GCH_Ryan 

please paste the code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRCvAFUwbG+gaG+kYGRgZQjikyxwyZY47MsUTiGBogc+CmAdmmCLYRVFGsDsIdRhB3gCicCFm98aEFYB2GYJooLSYQK4zxqo8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, #"Project Manager" = _t, #"Task 1 Start" = _t, #"Task 1 End" = _t, #"Task 2 Start " = _t, #"Milestone 1" = _t, #"Milestone 2" = _t, #"Task 2 End" = _t, #"Task 3 Start" = _t, #"Milestone 3" = _t, #"Task 3 End" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Project Manager", type text}, {"Task 1 Start", type date}, {"Task 1 End", type date}, {"Task 2 Start ", type date}, {"Milestone 1", type date}, {"Milestone 2", type date}, {"Task 2 End", type date}, {"Task 3 Start", type date}, {"Milestone 3", type text}, {"Task 3 End", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project", "Project Manager"}, "Attribute", "Value"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", Text.Trim, type text}}),
    #"Added Custom" = Table.AddColumn(#"Trimmed Text", "Category", each List.Intersect({{"Start", "End", "Milestone"}, Text.Split([Attribute], " ")}){0}),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Start","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," End","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value1", List.Distinct(#"Replaced Value1"[Category]), "Category", "Value")
in
    #"Pivoted Column"