I have a table that is currently set-up like this: ProjectProject ManagerTask 1 StartTask 1 EndTask 2 Start Milestone 1Milestone 2Task 2 EndTask 3 StartMilestone 3Task 3 EndProject 1PM 13/1/20203/5/20203/6/20203/7/20203/9/20203/10/20203/11/20203/15/20203/20/2020Project 2PM 2 Project 3 PM1 Project 4PM 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: ProjectProject ManagerEventStartEndMilestoneProject 1PM 1Task 1 3/1/20203/5/2020 Project 1PM 1Task 2 3/6/20203/10/2020 Project 1PM 1Milestone 1 3/7/2020Project 1PM 1Milestone 2 3/9/2020Project 1PM 1Task 3 3/11/20203/20/2020 Project 1PM 1Milestone 3 3/15/20202Project 2PM 2Task 1 Project 2PM 2Task 2 Project 2PM 2Milestone 1 Project 2PM 2Milestone 2 Project 2PM 2Task 3 Project 2PM 2Milestone 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 4PM 3Task 1 Project 4PM 3Task 2 Project 4PM 3Milestone 1 Project 4PM 3Milestone 2 Project 4PM 3Task 3 Project 4PM 3Milestone 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!

Hi GCH_Ryan Try this code on Advanced Editor (I assumed you are using TAB as separator):letSource = 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

Forum Discussion

GCH_Ryan

Frequent Visitor

6 years ago

Solved

Transpose Columns for Gantt 2.2.3

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!

Need Help

camargos88
6 years ago
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

3 Replies

camargos88
Community Champion
6 years ago
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

Super User

6 years ago

ImkeF , can you help?

ImkeF

Community Champion

6 years ago

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"

Forum Discussion

Transpose Columns for Gantt 2.2.3

3 Replies

Related content

Help Transposing

Transpose/ Transform Rows to Columns

Transpose table column values

Gantt 3.0.12.0

Multiple Rows transpose to Column