Excel Challenge 1037

ExcelChallenge1037.pq

let

  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],

  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Data", type text}}),

  Base = Table.AddColumn(

    #"Changed Type",

    "Answer",

    each Text.Lower(RemoveChars([Data], true, true, false, false)),

    type text

  ),

  Process = Table.AddColumn(

    Base,

    "List",

    each

      let

        base = List.Distinct(Base[Answer]),

        val = [Answer],

        alpha = Text.Combine(List.Sort(Splitter.SplitTextByRepeatedLengths(1)(val)), ""),

        remove = List.Select(base, each _ <> val),

        tx = List.Transform(remove, each Splitter.SplitTextByRepeatedLengths(1)(_)),

        sort = List.Transform(tx, each List.Sort(_)),

        combine = List.Transform(sort, each Text.Combine(_, "")),

        select = List.Transform(combine, each _ = alpha),

        zip = List.Zip({remove, select}),

        matches = List.Select(zip, each _{1} = true),

        tx_matches = List.Transform(matches, each _{0}),

        final =

          if List.Count(tx_matches) < 1 then

            null

          else

            Text.Combine(List.Sort(List.Combine({{val}, tx_matches})), " | ")

      in

        final,

    type text

  ),

  #"Filtered Rows" = Table.SelectRows(Process, each ([List] <> null)),

  #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"List"}),

  #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),

  #"Sorted Rows" = Table.Sort(#"Removed Duplicates", {{"List", Order.Ascending}})

in

  #"Sorted Rows"