— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — List top 4 teams on the basis of Total of Winners + Runners up count.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 78
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Top 4 Winner and Runner-up with Power Query
Power Query solution 1 for Top 4 Winner and Runner-up, proposed by Luan Rodrigues:
let
Fonte = Data,
g1 = Table.Group(Fonte, {"Winners"}, {{"Contagem", each Table.RowCount(_)}}),
g2 = Table.Group(Fonte, {"Runners-up"}, {{"Contagem", each Table.RowCount(_)}}),
Mesc = Table.NestedJoin(
g2,
{"Runners-up"},
g1,
{"Winners"},
"Linhas Agrupadas",
JoinKind.LeftOuter
),
gp = Table.ExpandTableColumn(Mesc, "Linhas Agrupadas", {"Contagem"}, {"Contagem.1"}),
g = Table.AddColumn(gp, "Total", each List.Sum({[Contagem], [Contagem.1]})),
Result = Table.RenameColumns(
Table.MaxN(g, "Total", each [Total] >= List.Min(List.MaxN(List.Distinct(g[Total]), 4))),
{{"Runners-up", "Team"}, {"Contagem.1", "Winners"}, {"Contagem", "Runners-up"}}
)[[Team], [Winners], [#"Runners-up"], [Total]]
in
ResultPower Query solution 2 for Top 4 Winner and Runner-up, proposed by Brian Julius:
Power Query solution 3 for Top 4 Winner and Runner-up, proposed by Brian Julius:
let
Source = TotalFinalsRaw,
Unpivot = Table.UnpivotOtherColumns(Source, {"Year"}, "Result", "Team"),
GroupCount = Table.Group(
Unpivot,
{"Result", "Team"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
Pivot = Table.Pivot(GroupCount, List.Distinct(GroupCount[Result]), "Result", "Count", List.Sum),
ReplaceNull = Table.ReplaceValue(Pivot, null, 0, Replacer.ReplaceValue, {"Winners", "Runners-up"}),
Total = Table.AddColumn(ReplaceNull, "Total", each [Winners] + [#"Runners-up"], Int64.Type),
Rank = Table.AddRankColumn(
Total,
"Rank",
{"Total", Order.Descending},
[RankKind = RankKind.Dense]
),
FilterNClean = Table.RemoveColumns(Table.SelectRows(Rank, each [Rank] <= 4), "Rank")
in
FilterNCleanPower Query solution 4 for Top 4 Winner and Runner-up, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(Source, {"Year"}, "W/R", "Team"),
TeamsList = List.Distinct(Unpivoted[Team]),
Grouped = Table.Group(Unpivoted, {"W/R"}, {{"All", each [Team]}}),
GroupList = List.Transform(
Grouped[All],
(l) => List.Transform(TeamsList, (y) => List.Count(List.Select(l, (x) => x = y)))
),
Generate = List.Last(
List.Generate(
() => [x = 1, y = GroupList{0}],
each [x] <= List.Count(GroupList),
each [
y = List.Transform(List.Zip({GroupList{[x]}, [y]}), each List.Sum({_{0}, _{1}})),
x = [x] + 1
],
each [y]
)
),
Accumulate = List.Accumulate(
{1 .. List.Count(GroupList) - 1},
GroupList{0},
(x, y) => List.Transform(List.Zip({x, GroupList{y}}), each List.Sum({_{0}, _{1}}))
),
MaxN = List.MaxN(List.Distinct(Generate), 4),
FilteredLists = List.Select(
List.Zip({TeamsList} & GroupList & {Generate}),
each List.Contains(MaxN, _{List.Count(GroupList) + 1})
),
SortedLists = Table.FromRows(
List.Sort(FilteredLists, {{each _{List.Count(GroupList) + 1}, 1}, {each _{0}, 1}}),
{"Team"} & Grouped[#"W/R"] & {"Total"}
)
in
SortedListsPower Query solution 5 for Top 4 Winner and Runner-up, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "WCtop4Teams"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Team"),
#"Grouped Rows" = Table.Group(
#"Unpivoted Columns",
{"Team"},
{
{"Winners", each List.Count(List.Select([Attribute], each _ = "Winners")), Int64.Type},
{"Runners-up", each List.Count(List.Select([Attribute], each _ = "Runners-up")), Int64.Type},
{"Total", each List.Count(_), Int64.Type}
}
),
#"Grouped Rows1" = Table.Group(
#"Grouped Rows",
{"Total"},
{
{
"All",
each _,
type table [Team = text, Winners = number, #"Runners-up" = number, Total = number]
}
}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows1", {{"Total", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] <= 4)[[All]],
Combine = Table.Combine(#"Filtered Rows"[All])
in
CombinePower Query solution 6 for Top 4 Winner and Runner-up, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = List.Transform(
List.Distinct(Source[Winners] & Source[#"Runners-up"]),
(a) => [
Team = a,
Winners = List.Count(List.Select(Source[Winners], each _ = a)),
#"Runners up" = List.Count(List.Select(Source[#"Runners-up"], each _ = a)),
Total = Winners + #"Runners up"
]
),
Finish =
let
f = each [Total],
m = List.Min(List.MaxN(List.Distinct(Transform, f), 4, f), 0, f)[Total]
in
Table.FromRecords(List.Sort(List.Select(Transform, each [Total] >= m), {f, 1}))
in
FinishPower Query solution 7 for Top 4 Winner and Runner-up, proposed by Venkata Rajesh:
let
Source = Data,
Unpivoted = Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Team"),
Pivoted = Table.Pivot(
Unpivoted,
List.Distinct(Unpivoted[Attribute]),
"Attribute",
"Year",
List.Count
),
Total = Table.AddColumn(Pivoted, "Total", each [Winners] + [#"Runners-up"], Int64.Type),
Sorted = Table.Sort(Total, {{"Total", Order.Descending}}),
Filtered = Table.SelectRows(
Sorted,
each [Total] >= List.Min(List.FirstN(List.Distinct(Sorted[Total]), 4))
)
in
FilteredPower Query solution 8 for Top 4 Winner and Runner-up, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Value"),
GroupOfGroup = Table.Group(
Unpivot,
{"Value"},
{{"tbl", each Table.Group(_, {"Attribute"}, {"W", Table.RowCount})}}
),
Expand = Table.ExpandTableColumn(GroupOfGroup, "tbl", {"Attribute", "W"}, {"Attribute", "W"}),
Pivot = Table.Pivot(Expand, List.Distinct(Expand[Attribute]), "Attribute", "W", List.Sum),
ReplaceNull = Table.ReplaceValue(Pivot, null, 0, Replacer.ReplaceValue, {"Winners", "Runners-up"}),
Sum = Table.AddColumn(ReplaceNull, "Total", each [Winners] + [#"Runners-up"], type number),
Filter = Table.SelectRows(
Sum,
each [Total] >= List.Reverse(List.MaxN(List.Distinct(Sum[Total]), 4)){0}
),
Sort = Table.Sort(Filter, {{"Total", Order.Descending}})
in
SortPower Query solution 9 for Top 4 Winner and Runner-up, proposed by Deron Huskey:
let
Source = Excel.CurrentWorkbook(){[Name = "WinnersRunnersUp"]}[Content],
ChgType = Table.TransformColumnTypes(
Source,
{{"Year", Int64.Type}, {"Winners", type text}, {"Runners-up", type text}}
),
ExclYear = Table.RemoveColumns(ChgType, {"Year"}),
Unpivot = Table.UnpivotOtherColumns(ExclYear, {}, "Attribute", "Value"),
CountIt = Table.Group(
Unpivot,
{"Value", "Attribute"},
{{"Count", each Table.RowCount(_), Int64.Type}}
),
PivotIt = Table.Pivot(CountIt, List.Distinct(CountIt[Attribute]), "Attribute", "Count", List.Sum),
NullTo0 = Table.ReplaceValue(PivotIt, null, 0, Replacer.ReplaceValue, {"Winners", "Runners-up"}),
AddTotal = Table.AddColumn(NullTo0, "Total", each [Winners] + [#"Runners-up"]),
ChgType2 = Table.TransformColumnTypes(AddTotal, {{"Total", type number}}),
DenseRank = Table.AddRankColumn(
ChgType2,
"Total Rank",
{"Total", Order.Descending},
[RankKind = RankKind.Dense]
),
Top4Only = Table.SelectRows(DenseRank, each [Total Rank] <= 4),
SortIt = Table.Sort(Top4Only, {{"Total Rank", Order.Ascending}}),
RemRank = Table.RemoveColumns(SortIt, {"Total Rank"})
in
RemRankSolving the challenge of Top 4 Winner and Runner-up with Excel
Excel solution 1 for Top 4 Winner and Runner-up, proposed by Rick Rothstein:
=LET(
u,
UNIQUE(
TOCOL(
B2:C22
)
),
w,
MAP(
u,
LAMBDA(
x,
COUNTIF(
B2:B22,
x
)
)
),
r,
MAP(
u,
LAMBDA(
x,
COUNTIF(
C2:C22,
x
)
)
),
t,
w+r,
SORT(
FILTER(
HSTACK(
u,
w,
r,
t
),
t>=LARGE(
UNIQUE(
t
),
4
)
),
4,
-1
)
)
Exploded Formula - Full Variable Names
-------------------------------------------------------
=LET(
uniq,
UNIQUE(
TOCOL(
B2:C22
)
),
winners,
MAP(
uniq,
LAMBDA(
x,
COUNTIF(
B2:B22,
x
)
)
),
runnerups,
MAP(
uniq,
LAMBDA(
x,
COUNTIF(
C2:C22,
x
)
)
),
total,
winners + runnerups,
SORT(
FILTER(
HSTACK(
uniq,
winners,
runnerups,
total
),
total >= LARGE(
UNIQUE(
total
),
4
)
),
4,
-1
)
)Excel solution 2 for Top 4 Winner and Runner-up, proposed by John V.:
=LET(
u,
UNIQUE(
TOCOL(
B2:C22
)
),
w,
COUNTIF(
B2:B22,
u
),
r,
COUNTIF(
C2:C22,
u
),
SORT(
FILTER(
HSTACK(
u,
w,
r,
w+r
),
w+r>LARGE(
UNIQUE(
w+r
),
5
)
),
4,
-1
)
)Excel solution 3 for Top 4 Winner and Runner-up, proposed by محمد حلمي:
=LET(U,UNIQUE(TOCOL(B2:C22)),
J,HSTACK(COUNTIF(B2:B22,U),COUNTIF(C2:C22,U)),
S,MMULT(J,{1;1}),
REDUCE(D2:G2,LARGE(UNIQUE(S),{1;2;3;4}),
LAMBDA(A,V,VSTACK(A,FILTER(HSTACK(U,J,S),S=V)))))Excel solution 4 for Top 4 Winner and Runner-up, proposed by 🇰🇷 Taeyong Shin:
=LET(d,B2:C22,r,TOCOL(d),p,PIVOTBY(r,TOCOL(IFNA(B1:C1,d)),r,ROWS,,0,,,-1),c,TAKE(p,,-1),GROUPBY(p,,,,0,{-4,-1},LARGE(UNIQUE(c),4)<=c,1))Excel solution 5 for Top 4 Winner and Runner-up, proposed by 🇰🇷 Taeyong Shin:
=LET(
Teams, UNIQUE(TOCOL(B2:C22)),
nums, DROP(REDUCE( "", Teams, LAMBDA(a,b,
LET(
hs, HSTACK(COUNTIF(B2:B22, b), COUNTIF(C2:C22, b)),
tot, SUM(hs),
VSTACK(a, HSTACK(hs, tot))
)
)), 1),
Total, TAKE(nums, , -1),
RankN, LARGE(UNIQUE(Total), MIN(4, COUNT(Total)) ),
SORT(FILTER(HSTACK(Teams, nums), Total >= RankN), {4,2}, {-1,1})
)Excel solution 6 for Top 4 Winner and Runner-up, proposed by Kris Jaganah:
=LET(a,A2:A22,b,B2:B22,c,C2:C22,d,VSTACK(HSTACK(b,LEFT(a,0)&B1),HSTACK(c,LEFT(a,0)&C1)),e,UNIQUE(CHOOSECOLS(d,1)),w,BYROW(e,LAMBDA(p,SUM((CHOOSECOLS(d,1)=p)*(CHOOSECOLS(d,2)="Winners")))),r,BYROW(e,LAMBDA(q,SUM((CHOOSECOLS(d,1)=q)*(CHOOSECOLS(d,2)="Runners-up")))),s,BYROW(e,LAMBDA(q,SUM((IF(CHOOSECOLS(d,1)=q,1,0))))),t,SORT(FILTER(HSTACK(e,w,r,s),s>=LARGE(UNIQUE(s),4)),4,-1),t)Excel solution 7 for Top 4 Winner and Runner-up, proposed by Julian Poeltl:
=LET(W,B2:B22,R,C2:C22,B,VSTACK(W,R),U,UNIQUE(B),L,LAMBDA(B,MAP(U,LAMBDA(A,SUM(--ISTEXT(FILTER(B,B=A)))))),WC,L(W),RC,L(R),A,WC+RC,T,SORT(HSTACK(U,WC,RC,A),4,-1),FILTER(T,TAKE(T,,-1)>LARGE(UNIQUE(A),5)))Excel solution 8 for Top 4 Winner and Runner-up, proposed by Julian Poeltl:
=LET(W,B2:B22,R,C2:C22,B,VSTACK(W,R),U,UNIQUE(B),WC,MAP(U,LAMBDA(A,SUM(--ISTEXT(FILTER(W,W=A))))),RC,MAP(U,LAMBDA(A,SUM(--ISTEXT(FILTER(R,R=A))))),A,WC+RC,T,SORT(HSTACK(U,WC,RC,A),4,-1),FILTER(T,TAKE(T,,-1)>LARGE(UNIQUE(A),5)))Excel solution 9 for Top 4 Winner and Runner-up, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_n, 4,
_d, A2:C22,
_t, DROP(_d, , 1),
_ut, UNIQUE(TOCOL(_t, , TRUE)),
_wc, COUNTIFS(INDEX(_t, , 1), _ut),
_rc, COUNTIFS(INDEX(_t, , 2), _ut),
_tc, _wc + _rc,
_l, LARGE(UNIQUE(_tc), MIN(_n, COUNT(_tc))),
_r, SORT(FILTER(HSTACK(_ut, _wc, _rc, _tc), _tc >= _l), {4, 3}, -1),
_r
)Excel solution 10 for Top 4 Winner and Runner-up, proposed by Timothée BLIOT:
=LET(Winners, B2:B22, RunnersUp, C2:C22, Years, A2:A22, AllCountries, UNIQUE(VSTACK(Winners,RunnersUp)),
Won, SORT(UNIQUE(HSTACK(Winners,BYROW(Winners,LAMBDA(a,SUMPRODUCT(1*(a=Winners)))))),2,-1),
Run, UNIQUE(HSTACK(RunnersUp,BYROW(RunnersUp,LAMBDA(a,SUMPRODUCT(1*(a=RunnersUp)))))),
Table, IFERROR(HSTACK(AllCountries,INDEX(Won,XMATCH(AllCountries,INDEX(Won,,1),0),2),INDEX(Run,XMATCH(AllCountries,INDEX(Run,,1),0),2)),0),
Total,
SORT(HSTACK(Table,INDEX(Table,,2)+INDEX(Table,,3)),{4,3,2},-1),
FILTER(Total,INDEX(Total,,4)>=LARGE(UNIQUE(INDEX(Total,,4)),4)))Excel solution 11 for Top 4 Winner and Runner-up, proposed by Duy Tùng:
=LET(
a,
B2:C22,
b,
TOCOL(
a
),
c,
PIVOTBY(
b,
TOCOL(
IF(
a>0,
B1:C1
)
),
b,
ROWS,
,
0,
-2,
1,
-1
),
d,
TAKE(
c,
,
-1
),
FILTER(
IF(
TAKE(
c,
1
)&TAKE(
c,
,
1
)="",
"Team",
c
),
d>LARGE(
UNIQUE(
d
),
5
)
)
)Excel solution 12 for Top &4 Winner and Runner-up, proposed by Stefan Olsson:
=LET(
_w,
{B2:B22},
_l,
{C2:C22},
_qw,
QUERY(
_w,
"Select Col1, Count(Col1), Year(Now())-2022 Group by Col1",
0
),
_ql,
QUERY(
_l,
"Select Col1, Year(Now())-2022, Count(Col1) Group by Col1",
0
),
_qwl,
QUERY(
{_qw;_ql},
"Select Col1, sum(Col2), sum(Col3), sum(Col2)+sum(Col3) where Col1<>'' group by Col1 label Col1 'Team', sum(Col2) 'Wins', sum(Col3) 'Runners Up', sum(Col2)+sum(Col3) 'Total'",
0
),
SORTN(
_qwl,
5,
3,
4,
false
)
)Excel solution 13 for Top 4 Winner and Runner-up, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(a,B2:B22,
b,C2:C22,
c,UNIQUE(TOCOL(B2:C22)),
d,COUNTIF(B2:C22,c),e,SORT(HSTACK(c,COUNTIF(a,c),COUNTIF(b,c),d),4,-1),
FILTER(e,TAKE(e,,-1)>=LARGE(UNIQUE(TAKE(e,,-1)),4)))Excel solution 14 for Top 4 Winner and Runner-up, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(
a,
B2:B22,
b,
C2:C22,
c,
UNIQUE(
TOCOL(
B2:C22
)
),
d,
COUNTIF(
B2:C22,
c
),
e,
SORT(
HSTACK(
c,
COUNTIF(
a,
c
),
COUNTIF(
b,
c
),
d
),
2,
-1
),
FILTER(
e,
TAKE(
e,
,
-1
)>=LARGE(
TAKE(
e,
,
-1
),
4
)
)
)Excel solution 15 for Top 4 Winner and Runner-up, proposed by El Badlis Mohd Marzudin:
=LET(
_w,B2:B22,
_ru,C2:C22,
_uteam,UNIQUE(VSTACK(_w,_ru)),
_wcount,COUNTIFS(_w,_uteam),
_rucount,COUNTIFS(_ru,_uteam),
_total,_wcount+_rucount,
_final,HSTACK(_uteam,_wcount,_rucount,_total),
SORT(FILTER(_final,_total>=LARGE(UNIQUE(CHOOSECOLS(_final,4)),4)),{4,2},{-1,-1}))Excel solution 16 for Top 4 Winner and Runner-up, proposed by Paolo Pozzoli:
=LET(allWins;B2:B22;
allRunners;C2:C22;
uniqs;UNICI(STACK.VERT(allWins;allRunners));
countWins;CONTA.SE(allWins;uniqs);
countRunup;CONTA.SE(allRunners;uniqs);
totals;MAP(countWins;countRunup;LAMBDA(cw;cr;cw+cr));
hstack;STACK.ORIZ(uniqs;countWins;countRunup;totals);
sorted;SCEGLI.RIGA(DATI.ORDINA.PER(hstack;SCEGLI.COL(hstack;4);-1);SEQUENZA(6));
sorted)
OUTPUT WITH SUBPRIORITY TO RUNNERS UP
=LET(allWins;B2:B22;
allRunners;C2:C22;
uniqs;UNICI(STACK.VERT(allWins;allRunners));
countWins;CONTA.SE(allWins;uniqs);
countRunup;CONTA.SE(allRunners;uniqs);
totals;MAP(countWins;countRunup;LAMBDA(cw;cr;cw+cr));
hstack;STACK.ORIZ(uniqs;countWins;countRunup;totals);
sorted;SCEGLI.RIGA(DATI.ORDINA.PER(hstack;SCEGLI.COL(hstack;4);-1;SCEGLI.COL(hstack;3);-1);SEQUENZA(6));
sorted)Solving the challenge of Top 4 Winner and Runner-up with SQL
SQL solution 1 for Top 4 Winner and Runner-up, proposed by Zoran Milokanović:
SELECT
F.TEAM
, F.WINNERS
,F.RUNNERS_UP
, F.TOTAL
FROM
(
SELECT
NVL(TW.TEAM, TR.TEAM) AS TEAM
,NVL(TW.WINNERS, 0) AS WINNERS
,NVL(TR.RUNNERS_UP, 0) AS RUNNERS_UP
,NVL(TW.WINNERS, 0) + NVL(TR.RUNNERS_UP, 0) AS TOTAL
,DENSE_RANK() OVER (ORDER BY NVL(TW.WINNERS, 0) + NVL(TR.RUNNERS_UP, 0) DESC) AS TOP_N
FROM
(
SELECT DW.WINNERS AS TEAM, COUNT(*) AS WINNERS
FROM DATA DW GROUP BY DW.WINNERS
) TW
FULL JOIN
(
SELECT DR.RUNNERS_UP AS TEAM, COUNT(*) AS RUNNERS_UP
FROM DATA DR GROUP BY DR.RUNNERS_UP
) TR ON TW.TEAM = TR.TEAM
) F
WHERE
F.TOP_N <= 4
ORDER BY
4 DESC, 2, 3, 1
;