Complex GANTT chart

[msimmonds22]

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

[DataInsights · Accepted solution]

@msimmonds22,

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 blanks

I 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