Excel BI Excel Challenge 190 – Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Add MaxPairs" = Table.AddColumn(Source, "Custom", each [
    a = List.Transform( Text.Split([Numbers], ","), each Text.Trim(_)),
    b =  List.Skip(a, 1 ) & {"null"},
    c = Table.RemoveLastN( Table.FromColumns( {a, b}), 1),
    d = Table.AddColumn( c, "PairSum", each Number.From( [Column1] ) + Number.From( [Column2])),
    e = Table.AddColumn(d, "Pair", each Text.Combine( { [Column1], [Column2] }, ", ")),
    f = List.Max( e[PairSum] ),
    g = Table.SelectRows( e, each [PairSum] = f),
    h = Table.SelectColumns( g, "Pair")
][h]),
    ExpandDIstinct = Table.DuplicateColumn( Table.Distinct( Table.ExpandTableColumn(#"Add MaxPairs", "Custom", {"Pair"}, {"Custom.Pair"})), "Custom.Pair", "Pair"),
    Split = Table.ExpandListColumn(Table.TransformColumns(ExpandDIstinct, {{"Custom.Pair", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Custom.Pair"),
    ReType = Table.TransformColumnTypes(Split,{{"Custom.Pair", Int64.Type}}),
    Group = Table.Group(ReType, {"Numbers"}, {{"All", each _, type table [Numbers=text, Custom.Pair=nullable number, Pair=nullable text, Count=number, Reverse=text]}}),
    AddCustom = Table.AddColumn(Group, "Custom", each Table.Distinct([All], {"Custom.Pair"})),
    Expand = Table.ExpandTableColumn(AddCustom, "Custom", {"Custom.Pair", "Pair"}, {"Custom.Pair", "Pair"}),
    RemoveOthers = Table.SelectColumns(Expand,{"Pair", "Numbers"}),
    DeDupe = Table.Distinct(RemoveOthers),
    Regroup = Table.Group(DeDupe, {"Numbers"}, {"All", each [Pair]}),
    Extract = Table.TransformColumns(Regroup, {"All", each Text.Combine(List.Transform(_, Text.From), ";"), type text})
in
    Extract