Power Query: nth rematch
I practice wrestling, and I help out my local association with doing some behind-the-scenes desk work.
One of the tasks that I have to do for tournaments is work on match pairing spreadsheets. Say there are two athletes that have a match series, a best out of three. The match list will have both their names listed three times. From there, I need to list those matches as Match 1/3, Match 2/3, and Match 3/3.
This was how it was done.
nth = Table.AddColumn(
#"Added Index",
"nth",
each
let
this = [
number = [Index],
div = [#"Group / Bracket"],
wrestler1 = [#"Wrestler 1"],
wrestler2 = [#"Wrestler 2"]
],
matches = Table.SelectRows(
buffer,
each [#"Group / Bracket"] = this[div]
and [#"Wrestler 1"] = this[wrestler1]
and [#"Wrestler 2"] = this[wrestler2]
),
nth = Table.RowCount(Table.SelectRows(
matches,
each [Index] <= this[number]
)),
bestOf3 = Table.RowCount(matches) > 1
in
if (bestOf3) then "Match " & Text.From(nth) & "/" & Text.From(Table.RowCount(matches)) else null
)