Forum Discussion
Complex GANTT chart
I have a need to greate a GANTT chart from data that is not, currently well structured for it.
In our data there can be up to 5 phases for a given project. However, all phases are listed in a single project record. The record does include the phase type and the start and end dates. We have 20 possible phase types. A single project can have 1 to 5 phases in any order. In other words, a given phase may be the only phase for a project or it may be the first or it may be the third.
I believe that I need to unpivot the data dynamically so that I have records for each project-phase along with the dates for that phase. However I have no idea how to unpivot multiple columns into one - if that is, indeed, what I need to do.
This is a rough exmaple of what the data can look like. Again, the desired output would be to have a GANTT chart with each phase charted by project.
| Project | Type1 | Start date1 | End Date1 | Type2 | Start Date2 | End Date2 | Type3 | Start Date3 | end Date3 | Type4 | Start Date4 | End Date4 | Type5 | Start Date5 | EndDate5 |
| 123abc | Flat | 1/1/2024 | 1/12/2024 | ||||||||||||
| 456def | Sheet Metal | 2/23/2024 | 3/4/2024 | Sigle ply | 3/5/2024 | 3/13/2024 | Foam | 3/18/2024 | 3/22/2024 | Coating | 3/24/2024 | 4/2/2024 | Tile | 4/3/2024 | 4/4/2024 |
| 789ghi | Single | 1/18/2024 | 2/12/2024 | Foam | 2/13/2024 | 2/28/2024 |
Here's a Power Query solution. Overview:
1. Unpivot all columns except Project
2. Split column Attribute (Non-Digit to Digit)
3. Pivot column Attribute.1 (Values Column = Value; Aggregate Value Function = Don’t Aggregate)
4. Filter out blanksI added a few steps to clean up inconsistent spelling.
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "lVA9D8IgFPwrhLkJ8qBaZ5NuTnUjDKhISbB1YPHfi1A+FgcTCPfuLvcuCIEpMHW94Q6PTvnwUEIJ7IAnCBmjv67sBOb9/q4fYZxmrT06a69cmEIiy6GM8Awna5xGL/eOdF8dtLjHVT0TNVQZSsPTqrxdTGJLbgAZXqzTkWFV3HzfvofhaGYbqywmOmmzCZq/2IpA0y1oxfrzSPkB", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ Project = _t, Type1 = _t, #"Start date1" = _t, #"End Date1" = _t, Type2 = _t, #"Start Date2" = _t, #"End Date2" = _t, Type3 = _t, #"Start Date3" = _t, #"end Date3" = _t, Type4 = _t, #"Start Date4" = _t, #"End Date4" = _t, Type5 = _t, #"Start Date5" = _t, EndDate5 = _t ] ), ChangeType = Table.TransformColumnTypes( Source, { {"Project", type text}, {"Type1", type text}, {"Start date1", type date}, {"End Date1", type date}, {"Type2", type text}, {"Start Date2", type date}, {"End Date2", type date}, {"Type3", type text}, {"Start Date3", type date}, {"end Date3", type date}, {"Type4", type text}, {"Start Date4", type date}, {"End Date4", type date}, {"Type5", type text}, {"Start Date5", type date}, {"EndDate5", type date} } ), UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"Project"}, "Attribute", "Value"), SplitColumn = Table.SplitColumn( UnpivotColumns, "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), {"Attribute.1", "Attribute.2"} ), ReplaceValue = Table.ReplaceValue( SplitColumn, "Start date", "Start Date", Replacer.ReplaceText, {"Attribute.1"} ), ReplaceValue2 = Table.ReplaceValue( ReplaceValue, "EndDate", "End Date", Replacer.ReplaceText, {"Attribute.1"} ), ReplaceValue3 = Table.ReplaceValue( ReplaceValue2, "end Date", "End Date", Replacer.ReplaceText, {"Attribute.1"} ), RenameColumn = Table.RenameColumns(ReplaceValue3, {{"Attribute.2", "Phase"}}), PivotColumns = Table.Pivot( RenameColumn, List.Distinct(RenameColumn[Attribute.1]), "Attribute.1", "Value" ), FilterRows = Table.SelectRows(PivotColumns, each ([Type] <> "" and [Type] <> " ")), ChangeType2 = Table.TransformColumnTypes( FilterRows, {{"Type", type text}, {"Start Date", type date}, {"End Date", type date}} ) in ChangeType2
1 Reply
Here's a Power Query solution. Overview:
1. Unpivot all columns except Project
2. Split column Attribute (Non-Digit to Digit)
3. Pivot column Attribute.1 (Values Column = Value; Aggregate Value Function = Don’t Aggregate)
4. Filter out blanksI added a few steps to clean up inconsistent spelling.
let Source = Table.FromRows( Json.Document( Binary.Decompress( Binary.FromText( "lVA9D8IgFPwrhLkJ8qBaZ5NuTnUjDKhISbB1YPHfi1A+FgcTCPfuLvcuCIEpMHW94Q6PTvnwUEIJ7IAnCBmjv67sBOb9/q4fYZxmrT06a69cmEIiy6GM8Awna5xGL/eOdF8dtLjHVT0TNVQZSsPTqrxdTGJLbgAZXqzTkWFV3HzfvofhaGYbqywmOmmzCZq/2IpA0y1oxfrzSPkB", BinaryEncoding.Base64 ), Compression.Deflate ) ), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ Project = _t, Type1 = _t, #"Start date1" = _t, #"End Date1" = _t, Type2 = _t, #"Start Date2" = _t, #"End Date2" = _t, Type3 = _t, #"Start Date3" = _t, #"end Date3" = _t, Type4 = _t, #"Start Date4" = _t, #"End Date4" = _t, Type5 = _t, #"Start Date5" = _t, EndDate5 = _t ] ), ChangeType = Table.TransformColumnTypes( Source, { {"Project", type text}, {"Type1", type text}, {"Start date1", type date}, {"End Date1", type date}, {"Type2", type text}, {"Start Date2", type date}, {"End Date2", type date}, {"Type3", type text}, {"Start Date3", type date}, {"end Date3", type date}, {"Type4", type text}, {"Start Date4", type date}, {"End Date4", type date}, {"Type5", type text}, {"Start Date5", type date}, {"EndDate5", type date} } ), UnpivotColumns = Table.UnpivotOtherColumns(ChangeType, {"Project"}, "Attribute", "Value"), SplitColumn = Table.SplitColumn( UnpivotColumns, "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0" .. "9"}, c), {"0" .. "9"}), {"Attribute.1", "Attribute.2"} ), ReplaceValue = Table.ReplaceValue( SplitColumn, "Start date", "Start Date", Replacer.ReplaceText, {"Attribute.1"} ), ReplaceValue2 = Table.ReplaceValue( ReplaceValue, "EndDate", "End Date", Replacer.ReplaceText, {"Attribute.1"} ), ReplaceValue3 = Table.ReplaceValue( ReplaceValue2, "end Date", "End Date", Replacer.ReplaceText, {"Attribute.1"} ), RenameColumn = Table.RenameColumns(ReplaceValue3, {{"Attribute.2", "Phase"}}), PivotColumns = Table.Pivot( RenameColumn, List.Distinct(RenameColumn[Attribute.1]), "Attribute.1", "Value" ), FilterRows = Table.SelectRows(PivotColumns, each ([Type] <> "" and [Type] <> " ")), ChangeType2 = Table.TransformColumnTypes( FilterRows, {{"Type", type text}, {"Start Date", type date}, {"End Date", type date}} ) in ChangeType2