Excel Challenge 1043

ExcelChallenge1043.pq

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), 
  #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Data", "Data - Copy"), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Duplicated Column", 
    {{"Data", type text}, {"Data - Copy", type text}}
  ), 
  #"Split Column by Space" = Table.ExpandListColumn(
    Table.TransformColumns(
      #"Changed Type", 
      {
        {
          "Data", 
          Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Data"
  ), 
  Tx = Table.AddColumn(
    #"Split Column by Space", 
    "Vals", 
    each 
      let
        string = [Data], 
        l = Text.ToList(string), 
        pairs = {{"{", "}"}, {"[", "]"}, {"(", ")"}}, 
        match = 
          if List.ContainsAll(l, pairs{0}) then
            Text.BetweenDelimiters(string, pairs{0}{0}, pairs{0}{1})
          else if List.ContainsAll(l, pairs{1}) then
            Text.BetweenDelimiters(string, pairs{1}{0}, pairs{1}{1})
          else if List.ContainsAll(l, pairs{2}) then
            Text.BetweenDelimiters(string, pairs{2}{0}, pairs{2}{1})
          else
            ""
      in
        match, 
    type text
  ), 
  #"Split Column by Colon" = Table.SplitColumn(
    Tx, 
    "Vals", 
    Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), 
    {"Vals.1", "Vals.2"}
  ), 
  #"Replaced Nulls with Blanks" = Table.ReplaceValue(
    #"Split Column by Colon", 
    null, 
    "", 
    Replacer.ReplaceValue, 
    {"Vals.2"}
  ), 
  #"Removed Other Columns" = Table.SelectColumns(
    #"Replaced Nulls with Blanks", 
    {"Index", "Data - Copy", "Vals.1", "Vals.2"}
  ), 
  #"Grouped Rows" = Table.Group(
    #"Removed Other Columns", 
    {"Index", "Data - Copy", "Vals.1"}, 
    {{"Vals", each Text.Combine([Vals.2], ", "), type nullable text}}
  ), 
  #"Pivoted Column" = Table.Pivot(
    #"Grouped Rows", 
    List.Distinct(#"Grouped Rows"[Vals.1]), 
    "Vals.1", 
    "Vals"
  ), 
  #"Reordered Columns" = Table.ReorderColumns(
    #"Pivoted Column", 
    {"Data - Copy", "APP", "NET", "SYS", "USR", ""}
  ), 
  #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns", {{"Data - Copy", "Data"}}), 
  #"Choose Final Columns" = Table.SelectColumns(
    #"Renamed Columns", 
    {"Data", "APP", "NET", "SYS", "USR"}
  )
in
  #"Choose Final Columns"