Excel BI Excel Power Query Challenge 151 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Dupe = Table.DuplicateColumn(Source, "Numbers", "Numbers2"),
    Split = Table.ExpandListColumn(Table.TransformColumns(Dupe, {{"Numbers2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Numbers2"),
    Trim = Table.TransformColumns(Split,{{"Numbers2", Text.Trim, type text}}),
    Group = Table.Group(Trim, {"Numbers"}, {{"All", each _, type table [Numbers=text, Numbers2=text]}}),
    AddIndex = Table.RemoveColumns( Table.AddColumn(Group, "Index", each Table.AddIndexColumn( [All], "Index", 1, 1 )), "All"),
    Expand = Table.ExpandTableColumn(AddIndex, "Index", {"Numbers2", "Index"}, {"Numbers2", "Index"}),
    AddIndexAll = Table.AddIndexColumn(Expand, "IndexAll", 1, 1, Int64.Type),
    Split2 = Table.ExpandListColumn(Table.TransformColumns(AddIndexAll, {{"Numbers2", Splitter.SplitTextByRepeatedLengths(1), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Numbers2"),
    Group2Distinct = Table.Group(Split2, {"IndexAll"}, {{"All", each Table.Distinct(_), type table [Numbers=text, Numbers2=nullable text, Index=number, IndexAll=number]}}),
    Expand2 = Table.ExpandTableColumn(Group2Distinct, "All", {"Numbers", "Numbers2", "Index"}, {"Numbers", "Numbers2", "Index"}),
    Group3 = Table.Group(Expand2, {"Numbers", "Numbers2"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [IndexAll=number, Numbers=nullable text, Numbers2=nullable text, Index=nullable number]}}),
    Eapand3 = Table.ExpandTableColumn(Group3, "All", {"Index", "IndexAll"}, {"Index", "IndexAll"}),
    Group4 = Table.Group(Eapand3, {"Numbers"}, {{"All", each _, type table [Numbers=nullable text, Numbers2=nullable text, Count=number, Index=nullable number, IndexAll=nullable number]}, {"Items", each List.Max([Index]), type nullable number}}),
    Expand4 = Table.RemoveColumns( Table.Distinct( Table.SelectRows( Table.ExpandTableColumn(Group4, "All", {"Numbers2", "Count"}, {"Numbers2", "Count"}), each [Count] = [Items] )), {"Count", "Items"}),
    Group5 = Table.Group(Expand4, {"Numbers"}, {{"All", each [Numbers2]}}),
    Extract = Table.TransformColumns(Group5, {"All", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    Rename = Table.RenameColumns(Extract,{{"Numbers", "Number"}, {"All", "Answer"}}),
    Join = Table.RemoveColumns( Table.Join( Source, "Numbers", Rename, "Number", JoinKind.LeftOuter), "Number")
in
    Join