Crispo Sunday Challenge - Brian Julius PQ Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="rng"]}[Content],
    Transpose = Table.Transpose(Source),
    Split = Table.ExpandListColumn(Table.TransformColumns(Transpose, {{"Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2"),
    CreatList = Table.RemoveColumns( Table.AddColumn(Split, "Custom", each List.Repeat({"1"}, Number.From( [Column2])) & {""}), "Column2"),
    Group = Table.Group(CreatList, {"Column1"}, {{"All", each [Custom], type list}}),
    Expand = Table.ExpandListColumn( Table.ExpandListColumn(Group, "All"), "All"),
    Group2 = Table.Group(Expand, {"Column1"}, {{"All", each _, type table [Column1=text, All=text]}, {"Count", each Table.RowCount(_), Int64.Type}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group2, "Index", each Table.AddIndexColumn( [All], "Index", 1, 1)), "All"),
    Expand2 = Table.RemoveColumns( Table.SelectRows( Table.ExpandTableColumn(AddIndex, "Index", {"All", "Index"}, {"All", "Index.1"}), each [Count] <> [Index.1]), "Count"),
    Pivot = Table.RemoveColumns( Table.Pivot(Expand2, List.Distinct(Expand2[Column1]), "Column1", "All"), "Index.1"), 
    Combine = Table.Combine( {Table.PromoteHeaders(Source), Pivot})
in
    Combine