Excel BI Challenge 223 - Brian Julius Solution

gistfile1.txt

let
    Source = Table.AddIndexColumn( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], "Index", 1, 1),
    ToRows = Table.ToRows( Table.RemoveColumns(Source, {"Sort Key", "Index"})),
    ToTable = Table.FromList(ToRows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Extract = Table.TransformColumns(ToTable, {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    AddIndex = Table.AddIndexColumn(Extract, "Index1", 1, 1, Int64.Type),
    Join = Table.RemoveColumns( Table.Join( AddIndex, "Index1", Table.SelectColumns( Source, {"Sort Key", "Index"}), "Index", JoinKind.LeftOuter), "Index1"),
    SpliToRows = Table.ExpandListColumn(Table.TransformColumns(Join, {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    Group = Table.Group(SpliToRows, {"Index", "Sort Key"}, {{"ListLength", each Table.RowCount(_), Int64.Type}, {"All", each [Column1], type list}}),
    AddToNums = Table.RemoveColumns( Table.AddColumn(Group, "ToNums", each List.Transform( [All], each Number.From(_) )), "All"),
    AddFinalList = Table.AddColumn(AddToNums, "FinalList", each [
        a = [ToNums],
        b = if [Sort Key] > [ListLength] then [ListLength] else [Sort Key],
        c = List.FirstN( a, b ),
        d = List.Sort( c ),
        e = List.RemoveFirstN( a, b ),
        f = List.Combine( {d, e}),
        g = List.Max( AddToNums[ListLength] ),
        h = List.Count(f),
        i = (g - h),
        j = List.Repeat( {null}, i),
        k = List.Combine( {f, j })
    ][k]),
    FinalTable = Table.FromRows( AddFinalList[FinalList] )
in
    FinalTable