Brian Julius Solution to Excel BI Challenge 41

gistfile1.txt

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LY1LDsIwDETvkjWXoMCKIiHaXdSFFVnYwiGSnarq7SlOdm9G84kxzFQyWFhOMbxWMxRxvmWW3ekOil+niVgFmzvCdtgJXVxI2SpDiz2hKicGFwNL35l3Qe1xOHba58jWgg989/6UuZLTtWip1OrDmj4OZ+H/7fID", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Members = _t]),
    Random = Table.AddColumn(Source, "Random", each Number.Random(), type number),
    Sort = Table.Sort(Random,{{"Random", Order.Ascending}}),
    Index = Table.AddIndexColumn(Sort, "Index", 1, 1, Int64.Type),
    Modulo = Table.RemoveColumns( Table.AddColumn(Index, "Modulo", each Number.Mod( [Index], 6 )), "Random"),
    Pivoted = Table.FromColumns( List.Transform( Table.ToColumns( Table.RemoveColumns( Table.Pivot(Table.TransformColumnTypes(Modulo, {{"Modulo", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Modulo, {{"Modulo", type text}}, "en-US")[Modulo]), "Modulo", "Members"), "Index")), each List.RemoveNulls( _ ))),
    Reserve = Table.RemoveColumns( Table.AddColumn(Pivoted, "Reserve", each List.Combine( {Pivoted[Column5], Pivoted[Column6]})), {"Column5", "Column6"}),
    Custom1 = Reserve,
    RemoveOC = Table.SelectColumns(Custom1,{"Reserve"}),
    Expand = Table.ExpandListColumn(RemoveOC, "Reserve"),
    DeDupe = Table.Distinct(Expand),
    ReserveTable = Table.SelectRows(DeDupe, each ([Reserve] <> null)),
    AddIdx = Table.AddIndexColumn(ReserveTable, "IndexR", 0, 1, Int64.Type),
    NewTable = Table.RemoveColumns( Table.AddIndexColumn( Custom1, "IndexN", 0, 1), "Reserve"),
    JoinTable = Table.Join( AddIdx, "IndexR" , NewTable, "IndexN", JoinKind.FullOuter),
    Remove = Table.RemoveColumns(JoinTable,{"IndexN", "IndexR"}),
    Rename = Table.RenameColumns(Remove,{{"Column1", "Team1"}, {"Column2", "Team2"}, {"Column3", "Team3"}, {"Column4", "Team4"}}),
    Reorder = Table.ReorderColumns(Rename,{"Team1", "Team2", "Team3", "Team4", "Reserve"})
in
   Reorder