Excel BI Excel/PQ Challenge 134 - Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DupeCol = Table.DuplicateColumn(Source, "Animals", "Animals2"),
    Split = Table.ExpandListColumn(Table.TransformColumns(DupeCol, {{"Animals2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Animals2"),
    #"Added Index" = Table.AddIndexColumn(Split, "SortOrder", 1, 1, Int64.Type),
    Trim = Table.TransformColumns(#"Added Index",{{"Animals2", Text.Trim, type text}}),
    Group = Table.Group(Trim, {"Animals", "Animals2"}, {{"All", each _, type table [Animals=text, Animals2=text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    AddIdx = Table.AddColumn(Group, "AddIdx", each Table.AddIndexColumn( [All], "Index", 1, 1)),
    Expand = Table.ExpandTableColumn(AddIdx, "AddIdx", {"Animals2", "Index", "SortOrder"}, {"Animals2.1", "Index", "SortOrder"}),
    Sort = Table.Sort(Expand,{{"SortOrder", Order.Ascending}}),
    AddConcat = Table.AddColumn(Sort, "Concat", each if [Count] = 1 then [Animals2] else [Animals2] & Text.From( [Index]) ),
    RemoveOther = Table.SelectColumns(AddConcat,{"Animals", "Concat"}),
    Regroup = Table.Group(RemoveOther, {"Animals"}, {"All", each [Concat]}),
    Extract = Table.RenameColumns( Table.TransformColumns(Regroup, {"All", each Text.Combine(List.Transform(_, Text.From), ", "), type text}), {"All", "Answer Expected"})
in
    Extract