Possible Combinations of Match from a list of Teams

Today, I will show various ways to generate possible match combinations from a list of teams.

There are 9 teams, and each team must play against the other 8 teams exactly once.

Therefore, we need to match the teams with one another so that each team plays against every other team only once.

Excercise-PQ.xlsx

Download XLSX • 25KB

Method-1:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = [Lst = List.Sort(Source[Clubs]), fin = Table.FromRows(List.TransformMany(Lst, (x) => List.Skip(Lst, List.PositionOf(Lst,x) + 1), (x,y) => {x,y}), {"Team1","Team2"})][fin]

in

 Result

Method-2:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = [Lst = List.Sort(Source[Clubs]), fx = (_) => List.PositionOf(Lst,_) + 1, fin = Table.FromRows(List.TransformMany(Lst, (f) => List.Range(Lst, fx(f), List.Count(Lst) - fx(f)), (x,y) => {x,y}),{"Team1","Team2"}) ][fin]

in

 Result

Method-3:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = [Lst = List.Sort(Source[Clubs]), pos = List.Positions(Lst), fin = Table.FromRows(List.Combine(List.Accumulate(pos, {}, (s,c) => s & { List.Transform( List.Range(Lst,c + 1, List.Count(Lst) - c), (f) => {Lst{c},f }) } )),{"Team1","Team2"}) ][fin]

in

 Result

Method-4:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 Result = [Lst = List.Sort(Source[Clubs]) , cnt = List.Count(Lst) - 1, fin = Table.FromRows(List.Skip(List.Generate(() => [a = {}, n = 0, c = n + 1], each [n] <= cnt, each [a = {Lst{[n]}, Lst{[c]}}, n = if [c] >= cnt then [n] + 1 else [n], c = if [c] >= cnt then [n] + 2 else [c] + 1], each [a])), {"Team1","Team2"}) ][fin]

in

 Result

Method-5:

Suggested by

Eric Laforce

let

 fxCombine = (l as list)=>let s=List.Skip(l) in if List.IsEmpty(s) then {} else List.Transform(s, each {l{0},_}) & @fxCombine(s),

 Result = Table.FromRows(fxCombine(List.Sort(Excel.CurrentWorkbook(){[Name="Table1"]}[Content][Clubs],0)), {"Team1","Team2"})

in

 Result

Method-6:

Suggested by Oleksandr Mynka

let

 src = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 ind = Table.AddIndexColumn(src,"idx"),

 lst = List.Buffer(src[Clubs]),

 add = Table.AddColumn(ind,"Team2", each List.Select(List.Skip(lst,[idx]),(x)=>x<>[Clubs])),

 del = Table.RemoveLastN(add,1),

 exp = Table.ExpandListColumn(del, "Team2")[[Clubs],[Team2]],

 res = Table.RenameColumns(exp,{"Clubs", "Team1"})

in

 res

Method-7:

Suggested by Rafael Gonzalez

let

 Source = Table_Question,

 TAC = Table.AddColumn,

 Tb1 = TAC(Source, "Index", each 1),

 Tb2 = Table.NestedJoin(Tb1, "Index", Tb1, "Index", "Join", 1)[[Clubs], [Join]],

 Exp = Table.ExpandTableColumn(Tb2, "Join", {"Clubs"}, {"Clubs.1"}),

 Lt = TAC(Exp, "Order", each List.Sort({[Clubs]} & {[Clubs.1]})),

 Rd = Table.Distinct(Lt, {"Order"})[[Clubs], [Clubs.1]],

 Filter = Table.SelectRows(Rd, each ([Clubs] <> [Clubs.1] )),

 Sorting = Table.Sort(Filter,{{"Clubs", 0}, {"Clubs.1", 0}})

in

 Sorting