List the Teams and the number of matches won by them. In case of equal score i.e. draw that won’t be considered.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 46
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Team Wins with Power Query
Power Query solution 1 for Count Team Wins, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
WhoWon = Table.AddColumn(
Source,
"Team",
each [
a = Text.Split([Result], "-"),
b = List.Transform(a, Number.From),
c = if b{0} > b{1} then [Team 1] else if b{0} < b{1} then [Team 2] else null
][c]
),
Teams = List.Distinct(Source[Team 1] & Source[Team 2]),
Wins = List.Transform(Teams, (f) => List.Count(List.Select(WhoWon[Team], (x) => x = f))),
Result = Table.Sort(Table.FromColumns({Teams, Wins}, {"Team", "Wins"}), {"Wins", 1})
in
ResultPower Query solution 2 for Count Team Wins, proposed by Luan Rodrigues:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
Div = Table.SplitColumn(
Fonte,
"Result",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{"Result.1", "Result.2"}
),
Tab = Table.AddColumn(
Div,
"Personalizar",
each
if Number.From([Result.2]) = Number.From([Result.1]) then
null
else if Number.From([Result.1]) > Number.From([Result.2]) then
[Team 1]
else if Number.From([Result.2]) > Number.From([Result.1]) then
[Team 2]
else
null
),
Team = Table.RenameColumns(
Table.FromList(List.Distinct(Tab[Team 1] & Tab[Team 2])),
{{"Column1", "Team"}}
),
Mesc = Table.NestedJoin(Team, {"Team"}, Tab, {"Personalizar"}, "Tabela", JoinKind.LeftOuter),
Result = Table.Sort(
Table.AddColumn(Mesc, "No. of Wins", each List.Count([Tabela][Personalizar]))[
[Team],
[No. of Wins]
],
{{"No. of Wins", Order.Descending}}
)
in
ResultPower Query solution 3 for Count Team Wins, proposed by Brian Julius:
let
Source = MatchesRaw,
Split = Table.SplitColumn(
Source,
"Result",
Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
{"Result.1", "Result.2"}
),
ChangeType = Table.TransformColumnTypes(
Split,
{{"Result.1", Int64.Type}, {"Result.2", Int64.Type}}
),
Winner = Table.AddColumn(
ChangeType,
"Team",
each
if [Result.1] > [Result.2] then
[Team 1]
else if [Result.2] > [Result.1] then
[Team 2]
else
null
),
Group = Table.Group(Winner, {"Team"}, {{"No. of Wins", each Table.RowCount(_), Int64.Type}}),
Filter = Table.SelectRows(Group, each ([Team] <> null)),
Merge = Table.NestedJoin(Source, {"Team 1"}, Filter, {"Team"}, "Temp", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Merge, "Temp", {"No. of Wins"}, {"No. of Wins"}),
RemoveCols = Table.RemoveColumns(Expand, {"Team 2", "Result"}),
Dupes = Table.Distinct(RemoveCols, {"Team 1"}),
Sort = Table.Sort(Dupes, {{"No. of Wins", Order.Descending}}),
Rename = Table.RenameColumns(Sort, {{"Team 1", "Team"}})
in
RenamePower Query solution 4 for Count Team Wins, proposed by Brian Julius:
Power Query solution 5 for Count Team Wins, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "WonMatches"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Result"}, "Attribute", "Team"),
#"Added Custom" = Table.AddColumn(
#"Unpivoted Columns",
"Winner",
each
if Expression.Evaluate([Result]) > 0 and [Attribute] = "Team 1" then
1
else if Expression.Evaluate([Result]) < 0 and [Attribute] = "Team 2" then
1
else
0
),
#"Grouped Rows" = Table.Group(
#"Added Custom",
{"Team"},
{{"No. of Wins", each List.Sum([Winner]), Int64.Type}}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"No. of Wins", Order.Descending}})
in
#"Sorted Rows"Power Query solution 6 for Count Team Wins, proposed by Antriksh Sharma:
let
Source = DataSource,
TableToRecord = Table.ToRecords(Source),
Transformation = List.Transform(
TableToRecord,
(Row) =>
let
TeamList = Record.ToList(Record.RemoveFields(Row, "Result")),
MatchesWonList = List.Transform(Text.Split(Row[Result], "-"), each Number.From(_)),
HasOneValue = List.Count(List.Distinct(MatchesWonList)) = 1,
Check =
if HasOneValue then
{TeamList} & {List.Repeat({null}, List.Count(TeamList))}
else
{TeamList} & {MatchesWonList},
NewTable = Table.Sort(
Table.FromColumns(Check, type table [Team = text, Score = Int64.Type]),
{"Score", Order.Descending}
),
Winner = Table.FirstN(NewTable, 1)
in
Winner
),
CombineTable = Table.Combine(Transformation),
GroupedRows = Table.Group(
CombineTable,
{"Team"},
{{"Count", each List.NonNullCount(_[Score]), Int64.Type}}
)
in
GroupedRowsPower Query solution 7 for Count Team Wins, proposed by Thomas DUCROQUETZ:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45Wcs5IzSlOTVTSUQpKTcxR8E1MKcpMAfIMdc2VYnWi0US9SstS80pKi4FMS11DsAIkIYRZJromWHS7KTgrOCUWJafm5OeBVJnpWoBVoYsjGWkENcgRqMQ3MycxD8Uac6gj0Q1AVg10pwFYEUIbunITXVMMS1BdbqpriaECyZXGukZKsbEA",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Team 1" = _t, #"Team 2" = _t, Result = _t]
),
UnpivotTeams = Table.UnpivotOtherColumns(Source, {"Result"}, "TeamType", "Team"),
GetWins = Table.ReplaceValue(
UnpivotTeams,
each [Result],
each
let
GoalsCurrentTeam = Number.From(
if [TeamType] = "Team 1" then
Text.BeforeDelimiter([Result], "-")
else
Text.AfterDelimiter([Result], "-")
),
GoalsOpponent = Number.From(
if [TeamType] = "Team 2" then
Text.BeforeDelimiter([Result], "-")
else
Text.AfterDelimiter([Result], "-")
)
in
if GoalsCurrentTeam > GoalsOpponent then 1 else 0,
Replacer.ReplaceValue,
{"Result"}
),
Grouped = Table.Group(GetWins, {"Team"}, {{"No. of Wins", each List.Sum([Result]), type number}})
in
GroupedPower Query solution 8 for Count Team Wins, proposed by Fowmy Abdulmuttalib:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Tlist = Table.FromList(List.Distinct(Source[Team 1] & Source[Team 2]), null, {"Teams"}),
WinTeams = Table.AddColumn(
Source,
"Winners",
each
let
T1G = Number.From(Text.BeforeDelimiter([Result], "-")),
T2G = Number.From(Text.AfterDelimiter([Result], "-")),
Twin = if T1G > T2G then [Team 1] else if T1G < T2G then [Team 2] else null
in
Twin
)[Winners],
Result = Table.AddColumn(
Tlist,
"No of Wins",
(i) => List.Count(List.Select(WinTeams, each _ = i[Teams]))
)
in
ResultSolving the challenge of Count Team Wins with Excel
Excel solution 1 for Count Team Wins, proposed by John V.:
=LET(r,
C2:C11,
d,
TEXTBEFORE(
r,
"-"
)-TEXTAFTER(
r,
"-"
),
u,
UNIQUE(
TOCOL(
A2:B11
)
),
SORT(HSTACK(u,
MAP(u,
LAMBDA(x,
SUM(--(IFS(
d>0,
A2:A11,
d<0,
B2:B11,
1,
)=x))))),
2,
-1))Excel solution 2 for Count Team Wins, proposed by محمد حلمي:
=LET(c,
C2:C11,
m,
A2:A11,
h,
B2:B11,
w,
-TEXTBEFORE(
c,
"-"
)+TEXTAFTER(
c,
"-"
),
a,
FILTER(
m,
-w>0
),
b,
FILTER(
h,
w>0
),
i,
VSTACK(
a,
b
),
o,
MAP(i,
LAMBDA(a,
SUM(--(i=a)))),
q,
UNIQUE(
VSTACK(
m,
h
)
),
z,
UNIQUE(
HSTACK(
i,
o
)
),
y,
UNIQUE(
IFNA(
IF(
XMATCH(
q,
INDEX(
z,
,
1
)
),
""
),
q
),
,
1
),
IFNA(
VSTACK(
z,
y
),
))Excel solution 3 for Count Team Wins, proposed by محمد حلمي:
=LET(
c,
C2:C11,
a,
TEXTBEFORE(
c,
"-"
)-TEXTAFTER(
c,
"-"
),
v,
IF(
a>0,
A2:A11,
IF(
a<0,
B2:B11
)
),
r,
UNIQUE(
TOCOL(
A2:B11
)
),
o,
MAP(r,
LAMBDA(x,
SUM(--(x=v)))),
SORT(
HSTACK(
r,
o
),
2,
-1
))Excel solution 4 for Count Team Wins, proposed by 🇰🇷 Taeyong Shin:
=LET(r,
C2:C11,
n,
TEXTBEFORE(
r,
"-"
)-TEXTAFTER(
r,
"-"
),
GROUPBY(IF(
n>0,
A2:A11,
B2:B11
),
--(n<>0),
SUM,
,
0,
-2))Excel solution 5 for Count Team Wins, proposed by 🇰🇷 Taeyong Shin:
=LET(Team1, A2:A11, Team2, B2:B11, Score, C2:C11,
Win, SWITCH( SIGN(EVAL(Score)), 1, Team1, -1, Team2, NA()),
Uteam, UNIQUE(VSTACK(Team1, Team2)),
Body, HSTACK(Uteam, MAP(Uteam, LAMBDA(m, COUNT( 1/(Win=m) ) )) ),
VSTACK( {"Team","No. of Wins"}, SORT(Body, 2, -1) )
)
EVAL = LAMBDA(values, EVALUATE(values) )Excel solution 6 for Count Team Wins, proposed by Julian Poeltl:
=LET(
T,
A2:A11,
TT,
B2:B11,
R,
C2:C11,
A,
--TEXTAFTER(
R,
"-"
),
B,
--TEXTBEFORE(
R,
"-"
),
W,
IF(
A>B,
TT,
IF(
A=B,
"",
T
)
),
U,
UNIQUE(
VSTACK(
T,
TT
)
),
RE,
SORT(
HSTACK(
U,
MAP(
U,
LAMBDA(
A,
SUM(
IF(
NOT(
ISERR(
FILTER(
W,
W=A
)
)
),
1,
0
)
)
)
)
),
2,
-1
),
RE
)Excel solution 7 for Count Team Wins, proposed by Aditya Kumar Darak 🇮🇳:
= LET(
_t1,
A2:A11,
_t2,
B2:B11,
_ts1,
--TEXTBEFORE(
C2:C11,
"-"
),
_ts2,
--TEXTAFTER(
C2:C11,
"-"
),
_calc,
IFS(
_ts1 > _ts2,
_t1,
_ts1 < _ts2,
_t2,
TRUE,
""
),
_ut,
UNIQUE(
VSTACK(
_t1,
_t2
)
),
_win,
MAP(_ut,
LAMBDA(a,
SUM(--(_calc = a)))),
SORT(
HSTACK(
_ut,
_win
),
2,
-1
))Excel solution 8 for Count Team Wins, proposed by Timothée BLIOT:
=LET(
Array,
A2:C11,
team1,
INDEX(
Array,
,
1
),
team2,
INDEX(
Array,
,
2
),
Result,
INDEX(
Array,
,
3
),
Teams,
UNIQUE(
VSTACK(
team1,
team2
)
),
Goals,
HSTACK(
VALUE(
TEXTBEFORE(
Result,
"-"
)
),
VALUE(
TEXTAFTER(
Result,
"-"
)
)
),
Victories,
IF(
INDEX(
Goals,
,
1
)>INDEX(
Goals,
,
2
),
{1,
0},
IF(
INDEX(
Goals,
,
1
)=INDEX(
Goals,
,
2
),
{0,
0},
{0,
1}
)
),
FilterArray,
HSTACK(VSTACK(
team1,
team2
),
(VSTACK(
INDEX(
Victories,
,
1
),
INDEX(
Victories,
,
2
)
))),
TeamsWins,
BYROW(
Teams,
LAMBDA(
r,
SUM(
FILTER(
INDEX(
FilterArray,
,
2
),
INDEX(
FilterArray,
,
1
) = r,
""
)
)
)
),
Output,
HSTACK(
Teams,
TeamsWins
),
SORT(
Output,
2,
-1
))Excel solution 9 for Count Team Wins, proposed by Hussein SATOUR:
=LET(a, A2:C11,
b, BYROW(a, LAMBDA(x, LET(y, TEXTSPLIT(INDEX(x,,3),"-")*1, z, INDEX(CHOOSECOLS(x,1,2),, IF(SUM(y)/2 = INDEX(y,,1), 3, XMATCH(MAX(y), y))), z))),
c, UNIQUE(TOCOL(CHOOSECOLS(a, 1, 2))), d, ARRAYTOTEXT(b),
SORT(HSTACK(c, BYROW(c, LAMBDA(x, (LEN(d) - LEN(SUBSTITUTE(d, x, ""))) / LEN(x)))), 2,-1))Excel solution 10 for Count Team Wins, proposed by Duy Tùng:
=LET(
a,
MMULT(
-TEXTSPLIT(
ARRAYTOTEXT(
C2:C11
),
"-",
", "
),
{-1;1}
),
GROUPBY(
TOCOL(
A2:B11
),
TOCOL(
--IF(
{1,
0},
a>0,
a<0
)
),
SUM,
,
0,
-2
)
)Excel solution 11 for Count Team Wins, proposed by Bhavya Gupta:
=LET(Team_1,
A2:A11,
Team_2,
B2:B11,
Result,
C2:C11,
U_teams,
UNIQUE(
TOCOL(
HSTACK(
Team_1,
Team_2&
)
)
),
SORT(HSTACK(U_teams,
REDUCE(0,
MAP(
Team_1,
Team_2,
Result,
LAMBDA(
t_1,
t_2,
r_s,
LET(
a,
TEXTSPLIT(
r_s,
"-"
)*1,
IF(
COLUMNS(
UNIQUE(
a,
TRUE
)
)=1,
0,
DROP(
SORTBY(
HSTACK(
t_1,
t_2
),
a
),
,
1
)
)
)
)
),
LAMBDA(a,
b,
(b=U_teams)*1+a))),
2,
-1))Excel solution 12 for Count Team Wins, proposed by Charles Roldan:
=LET(
_Matches,
A2:B11,
_Score,
C2:C11,
_Goals,
--TEXTSPLIT(
ARRAYTOTEXT(
_Score
),
"-",
", "
),
_Wins,
--(_Goals > BYROW(
_Goals,
MIN
)),
GROUPBY(
TOCOL(
_Matches
),
TOCOL(
_Wins
),
SUM,
,
0,
-2
)
)Excel solution 13 for Count Team Wins, proposed by Charles Roldan:
=LET(
_Matches,
A2:B11,
_Score,
C2:C11,
_Goals,
--HSTACK(
TEXTBEFORE(
_Score,
"-"
),
TEXTAFTER(
_Score,
"-"
)
),
_GameWinner,
_Goals > BYROW(
_Goals,
MIN
),
_Teams,
UNIQUE(
TOCOL(
_Matches
)
),
_Winners,
FILTER(
TOROW(
_Matches
),
TOROW(
_GameWinner
)
),
_Wins,
BYROW(--(_Teams = _Winners),
SUM),
SORT(
HSTACK(
_Teams,
_Wins
),
2,
-1
)
)Excel solution 14 for Count Team Wins, proposed by Oscar Javier Rosero Jiménez:
=LET(
_t1,
A2:A11,
_t2,
B2:B11,
_r,
C2:C11,
_u,
UNIQUE(
VSTACK(
_t1,
_t2
)
),
_res,
--TEXTSPLIT(
TEXTJOIN(
",",
0,
_r
),
"-",
","
),
a,
0+(CHOOSECOLS(
_res,
1
)>CHOOSECOLS(
_res,
2
)),
b,
0+(CHOOSECOLS(
_res,
1
)b,
a,
a Excel solution 15 for Count Team Wins, proposed by Jardiel Euflázio:
=LET(
a,
A2:A11,
b,
B2:B11,
c,
C2:C11,
d,
TEXTBEFORE(
c,
"-"
),
e,
TEXTAFTER(
c,
"-"
),
f,
IF(
d+0>e+0,
a,
IF(
e+0>d+0,
b
)
),
g,
UNIQUE(
TOCOL(
HSTACK(
a,
b
)
)
),
SORT(
HSTACK(
g,
BYROW(
g,
LAMBDA(
g,
IFERROR(
ROWS(
FILTER(
f,
f=g
)
),
0
)
)
)
),
2,
-1
)
)Excel solution 16 for Count Team Wins, proposed by Sarun Chimamphant:
=LET(
ab,
TOCOL(
A2:B11,
,
1
),
c,
C2:C11,
l,
--TEXTBEFORE(
c,
"-"
),
r,
--TEXTAFTER(
c,
"-"
),
wl,
TOCOL(
--HSTACK(
l>r,
r>l
),
,
1
),
d,
UNIQUE(
ab
),
e,
BYROW(
d,
LAMBDA(
r,
SUM(
IF(
ab=r,
wl,
0
)
)
)
),
SORT(
HSTACK(
d,
e
),
2,
-1
)
)Excel solution 17 for Count Team Wins, proposed by Miguel Angel Franco García:
=LET(
a;
ABS(
EXTRAE(
C2:C11;
1;
ENCONTRAR(
"-";
C2:C11
)-1
)
);
b;
ABS(
DERECHA(
C2:C11;
LARGO(
C2:C11
)-ENCONTRAR(
"-";
C2:C11
)
)
);
resul;
ORDENAR(
ENCOL(
APILARV(
SI(
a-b>0;
A2:A11;
NOD()
);
SI(
a-b<0;
B2:B11;
NOD()
)
);
3
)
);
solos;
UNICOS(
A2:A11
);
resul2;
APILARH(
resul;
FRECUENCIA(
COINCIDIR(
resul;
resul;
0
);
COINCIDIR(
resul;
resul;
0
)
)
);
FILTRAR(
SI.ERROR(
resul2;
""
);
INDICE(
SI.ERROR(
resul2;
""
);
;
2
)<>0
)
)Solving the challenge of Count Team Wins with Python in Excel
Python in Excel solution 1 for Count Team Wins, proposed by Alejandro Campos:
df = xl("A1:C11", headers=True)
team_wins = {team: 0 for team in set(df['Team 1']).union(df['Team 2'])}
for _, row in df.iterrows():
score_team1, score_team2 = map(int, row['Result'].split('-'))
winner = row['Team 1'] if score_team1 > score_team2 else row['Team 2'] if score_team2 > score_team1 else None
if winner: team_wins[winner] += 1
result_df = pd.DataFrame(list(team_wins.items()), columns=['Team', 'Wins']).sort_values(by='Wins', ascending=False).reset_index(drop=True)
Solving the challenge of Count Team Wins with DAX
DAX solution 1 for Count Team Wins, proposed by Zoran Milokanović:
DEFINE
VAR T = ADDCOLUMNS(Input,
"G1", VAR R = Input[Result] RETURN CONVERT(LEFT(R, FIND("-", R, 1, 0) - 1), INTEGER),
"G2", VAR R = Input[Result] RETURN CONVERT(MID(R, FIND("-", R, 1, 0) + 1, LEN(R)), INTEGER)
)
EVALUATE
GROUPBY(
UNION(
SELECTCOLUMNS(T, "Team", Input[Team 1], "N", IF([G1] > [G2], 1, 0)),
SELECTCOLUMNS(T, "Team", Input[Team 2], "N", IF([G2] > [G1], 1, 0))
),
[Team],
"No. of Wins",
SUMX(CURRENTGROUP(), [N])
)
ORDER BY
[No. of Wins] DESC
&&