Complex GANTT chart

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