Excel BI Power Query Challenge #110 Brian Julius Solution

gistfile1.txt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Headers = Table.ColumnNames( Source ),
    ToRecs = Table.ToRecords( Source ),
    ToTable = Table.FromList(ToRecs, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    AddResultRemain = Table.AddColumn(ToTable, "ResultRemain", each [
        a = Text.Split( List.First( Record.ToList( [Column1] )), ", "),
        b = List.Skip( Record.ToList( [Column1]), 1),
        c = List.Count(a),
        d = List.Count(List.RemoveNulls(b)),
        e = List.Count(b),
        Remain = if c > d then Text.Combine( List.Skip(a, d), ", ") else null ,
        RptList = if Remain <> null then a else List.Repeat( a, Number.RoundUp(Value.Divide( d, c ) )),
        RptListTrim = List.FirstN( RptList, d),
        f = List.Skip( Record.FieldNames( [Column1]), 1),
        g = Table.ToColumns( Table.SelectRows( Table.FromColumns( {f,b}), each [Column2] <> null )){0},
        h = Table.FromColumns( {g, RptListTrim})
    ][[h], [Remain]]),
    Expand1 = Table.ExpandRecordColumn(AddResultRemain, "ResultRemain", {"h", "Remain"}, {"h", "Remain"}),
    ExpandString = Table.ExpandRecordColumn(Expand1, "Column1", {"Names"}, {"Column1.Names"}),
    Expand2 = Table.ExpandTableColumn(ExpandString, "h", {"Column1", "Column2"}, {"h.Column1", "h.Column2"}),
    AddPivotName = Table.AddColumn(Expand2, "PivotName", each if [h.Column1] = null then "zzz_"&[Column1.Names] else [h.Column1]),
    RemCols = Table.RemoveColumns(AddPivotName,{"h.Column1", "Remain"}),
    Pivot = Table.RenameColumns( Table.Pivot(RemCols, List.Distinct(RemCols[PivotName]), "PivotName", "h.Column2"), {"Column1.Names", "Names"}),
    EmptyTab = #table( Headers, {}),
    Append = Table.SelectColumns( Table.Combine( { EmptyTab, Pivot}), Headers),
    SortOrder = Table.AddIndexColumn( Table.SelectColumns(Source, "Names"), "Index", 1, 1),
    Join = Table.Join( Append, "Names", SortOrder, "Names"),
    Sort = Table.RemoveColumns( Table.Sort(Join,{{"Index", Order.Ascending}}), "Index")
in
    Sort