— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — Since, yesterday’s challenge was very tough, today I am reducing the difficult level. List top 4 FIFA WC champions in the descending order of no. of times teams have won the world cup along-with their years of winning. Your formula need not be different from others as long as you have worked out your formula independently)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 71
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Top 4 FIFA WC Champions with Power Query
Power Query solution 1 for Top 4 FIFA WC Champions, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
TopN = 4,
Group1 = Table.Group(
Source,
"Champion",
{{"Won", Table.RowCount}, {"Years", each Text.Combine(List.Transform([Year], Text.From), ", ")}}
),
Group2 = Table.Group(Group1, "Won", {"All", each _}),
TopNFiltered = Table.MaxN(Group2, "Won", TopN),
Return = Table.Combine(TopNFiltered[All])
in
ReturnPower Query solution 2 for Top 4 FIFA WC Champions, proposed by Luan Rodrigues:
let
Fonte = Data,
Group = Table.SelectRows(
Table.AddRankColumn(
Table.Sort(
Table.Group(
Fonte,
{"Champion"},
{{"Times Won", each Table.RowCount(_)}, {"Years of Winning", each [Year]}}
),
{{"Times Won", Order.Descending}}
),
"Rank",
{"Times Won", Order.Descending}
),
each [Rank] <= 4
)[[Champion], [Times Won], [Years of Winning]],
Result = Table.TransformColumns(
Group,
{"Years of Winning", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
)
in
ResultPower Query solution 3 for Top 4 FIFA WC Champions, proposed by Brian Julius:
let
Source = WinsRaw,
Group = Table.Group(
Source,
{"Champion"},
{
{"Times Won", each Table.RowCount(_), Int64.Type},
{"All", each _, type table [Year = nullable text, Champion = nullable text]}
}
),
List = Table.AddColumn(Group, "Years", each [All][Year]),
Extract = Table.RemoveColumns(
Table.TransformColumns(
List,
{"Years", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
),
"All"
),
Rank = Table.AddColumn(
Extract,
"Rank",
each Table.AddRankColumn(
Extract,
"RankWins",
{"Times Won", Order.Descending},
[RankKind = RankKind.Dense]
)
)[Rank],
ToTable = Table.FromList(Rank, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Clean = Table.RemoveColumns(
Table.SelectRows(
Table.Distinct(
Table.ExpandTableColumn(
ToTable,
"Column1",
{"Champion", "Times Won", "Years", "RankWins"},
{"Champion", "Times Won", "Years", "RankWins"}
)
),
each [RankWins] <= 4
),
"RankWins"
),
Sort = Table.Sort(Clean, {{"Times Won", Order.Descending}, {"Champion", Order.Ascending}})
in
SortPower Query solution 4 for Top 4 FIFA WC Champions, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Champion"},
{
{"Times Won", each Table.RowCount(_)},
{"Years of Winning", each Text.Combine(List.Transform([Year], each Text.From(_)), ", ")}
}
),
Grouped_2 = Table.Group(
Grouped,
{"Times Won"},
{{"All", each _, type table [Champion = text, Times Won = number, Years of Winning = text]}}
),
Sorted = Table.FirstN(Table.Sort(Grouped_2, {{"Times Won", Order.Descending}}), 4),
RemovedOtherColumns = Table.SelectColumns(Sorted, {"All"}),
Expanded = Table.Sort(
Table.ExpandTableColumn(
RemovedOtherColumns,
"All",
{"Champion", "Times Won", "Years of Winning"},
{"Champion", "Times Won", "Years of Winning"}
),
{{"Times Won", Order.Descending}, {"Champion", Order.Ascending}}
)
in
ExpandedPower Query solution 5 for Top 4 FIFA WC Champions, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "WorldCupWins"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Champion"},
{
{"Times of Winning", each Table.RowCount(_), Int64.Type},
{"Years of Winning", each Text.Combine(List.Transform([Year], each Text.From(_)), ", ")}
}
),
#"Filtered Rows" = Table.SelectRows(
#"Grouped Rows",
each [Times of Winning]
>= List.Sort(List.Distinct(#"Grouped Rows"[Times of Winning]), Order.Descending){3}
),
#"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Times of Winning", Order.Descending}})
in
#"Sorted Rows"Power Query solution 6 for Top 4 FIFA WC Champions, proposed by Victor Momoh (MVP, MOS, R.Eng):
let
Source = Excel.CurrentWorkbook(){[Name = "WCWinners"]}[Content],
OrderColumns = Table.ReorderColumns(Source, {"Champion", "Year"}),
#"Changed Type" = Table.TransformColumnTypes(OrderColumns, {{"Year", type text}}),
groupByWinner = Table.Group(
#"Changed Type",
{"Champion"},
{
{"NumberOfTimes", each Table.RowCount(_), Int64.Type},
{"All years", each Text.Combine(_[Year], ", ")}
}
),
SortDescending = Table.Sort(groupByWinner, {{"NumberOfTimes", Order.Descending}}),
FilterToTopN = Table.SelectRows(
SortDescending,
each [NumberOfTimes]
>= Number.From(List.Sort(SortDescending[NumberOfTimes], Order.Descending){3})
),
RemoveCountCol = Table.RemoveColumns(FilterToTopN, {"NumberOfTimes"})
in
RemoveCountColPower Query solution 7 for Top 4 FIFA WC Champions, proposed by Venkata Rajesh:
let
Source = Data,
#"Grouped Rows" = Table.Group(
Source,
{"Champion"},
{
{"Times Won", each Table.RowCount(_), Int64.Type},
{
"Years",
each Text.Combine(List.Transform([Year], each Text.From(_)), ", "),
type nullable text
}
}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"Times Won", Order.Descending}}),
#"Filtered Rows" = Table.SelectRows(
#"Sorted Rows",
each ([Times Won] > List.Max(#"Sorted Rows"[Times Won]) - 4)
)
in
#"Filtered Rows"Power Query solution 8 for Top 4 FIFA WC Champions, proposed by Abdoul Karim N.:
let
Source = Excel.CurrentWorkbook(){[Name = "TWordlCup"]}[Content],
ChangedType = Table.TransformColumnTypes(Source, {{"Year", type text}, {"Champion", type text}}),
Intermediate = [
Grouping = Table.Sort(
Table.Group(
ChangedType,
{"Champion"},
{
{"Time Wons", each Table.RowCount(_), Int64.Type},
{"Years of Wins", each Text.Combine([Year], " ,")}
}
),
{"Time Wons", Order.Descending}
),
Placeholder = List.Last(List.FirstN(List.Distinct(Grouping[Time Wons]), 4)),
Final = Table.SelectRows(Grouping, each [Time Wons] >= Placeholder)
][Final]
in
IntermediateSolving the challenge of Top 4 FIFA WC Champions with Excel
Excel solution 1 for Top 4 FIFA WC Champions, proposed by 🇰🇷 Taeyong Shin:
=LET(
Y,
A2:A22,
Chmp,
B2:B22,
cnt,
COUNTIF(
Chmp,
Chmp
),
RankN,
LARGE(
UNIQUE(
cnt
),
4
),
arr,
SORT(
HSTACK(
Chmp,
cnt
),
2,
-1
),
Cw,
UNIQUE(
TAKE(
arr,
XMATCH(
RankN,
INDEX(
arr,
,
2
),
,
-1
),
2
)
),
SORT(
HSTACK(
Cw,
MAP(
INDEX(
Cw,
,
1
),
LAMBDA(
m,
ARRAYTOTEXT(
FILTER(
Y,
Chmp = m
)
)
)
)
),
{2,
1},
{-1,
1}
)
)Excel solution 2 for Top 4 FIFA WC Champions, proposed by 🇰🇷 Taeyong Shin:
=LET(a,B2:B22,n,COUNTIF(a,a),DROP(GROUPBY(a,A2:A22,HSTACK(ROWS,ARRAYTOTEXT),,0,-2,LARGE(UNIQUE(n),4)<=n),1))Excel solution 3 for Top 4 FIFA WC Champions, proposed by Julian Poeltl:
=LET(
Y,
A2:A22,
C,
B2:B22,
U,
UNIQUE(
C
),
W,
MAP(
U,
LAMBDA(
A,
TEXTJOIN(
", ",
,
FILTER(
Y,
C=A
)
)
)
),
N,
LEN(
W
)-LEN(
SUBSTITUTE(
W,
",",
""
)
)+1,
T,
HSTACK(
U,
N,
W
),
SORT(
SORT(
FILTER(
T,
N>=LARGE(
N,
4
)
),
1
),
2,
-1
)
)Excel solution 4 for Top 4 FIFA WC Champions, proposed by Timothée BLIOT:
=LET(Years, A2:A22, Champions, B2:B22,
Wins, SORT(HSTACK(Champions, MAP(Champions, LAMBDA(a, SUMPRODUCT(1*(Champions=a)) )), Years),2,-1),
Table, HSTACK(UNIQUE(INDEX(Wins,,1)), MAP(UNIQUE(INDEX(Wins,,1)), LAMBDA(a, UNIQUE(FILTER(INDEX(Wins,,2),a=INDEX(Wins,,1))) )), MAP(UNIQUE(INDEX(Wins,,1)), LAMBDA(a, ARRAYTOTEXT( FILTER(INDEX(Wins,,3),a=INDEX(Wins,,1))) )) ),
FILTER(Table, LARGE(INDEX(Table,,2),5)<=INDEX(Table,,2)))Excel solution 5 for Top 4 FIFA WC Champions, proposed by Hussein SATOUR:
=LET(y, A2:A22, c, B2:B22, a, UNIQUE(c), b, COUNTIF(c, a),
d, MAP(a, LAMBDA(x, ARRAYTOTEXT(FILTER(y, c = x)))),
SORT(FILTER(HSTACK(a, b, d), b>= LARGE(UNIQUE(b),4)), {2,1}, {-1,1}))Excel solution 6 for Top 4 FIFA WC Champions, proposed by Bhavya Gupta:
=LET(Y,
A2:A22,
C,
B2:B22,
U,
UNIQUE(
C
),
M,
MAP(U,
LAMBDA(x,
SUM(--(C=x))&"-"&ARRAYTOTEXT(
FILTER(
Y,
C=x
)
))),
a,
TEXTBEFORE(
M,
"-"
)*1,
SORT(
FILTER(
HSTACK(
U,
a,
TEXTAFTER(
M,
"-"
)
),
a>LARGE(
UNIQUE(
a
),
5
)
),
{2,
1},
{-1,
1}
))Excel solution 7 for Top 4 FIFA WC Champions, proposed by Charles Roldan:
=LAMBDA(
Data,
LET(
Year,
INDEX(
Data,
,
1
),
Team,
INDEX(
Data,
,
2
),
Elite,
UNIQUE(
FILTER(
Team,
ISNA(
XMATCH(
Team,
UNIQUE(
Team,
,
TRUE
)
)
)
)
),
WinCount,
COUNTIF(
Team,
Elite
),
Banner,
MAP(
Elite,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
Year,
Team=x
)
)
)
),
SORT(
HSTACK(
Elite,
WinCount,
Banner
),
{2,
1},
{-1,
1}
)
)
)(A2:B22)Excel solution 8 for Top 4 FIFA WC Champions, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(
a,
A2:A22,
b,
B2:B22,
c,
UNIQUE(
b
),
d,
COUNTIF(
b,
c
),
e,
SORTBY(
c,
d,
-1
),
f,
FILTER(
e,
SORT(
d,
,
-1
)>=LARGE(
d,
4
)
),
HSTACK(
f,
MAP(
f,
LAMBDA(
x,
TEXTJOIN(
",",
1,
FILTER(
a,
b=x
)
)
)
)
)
)Excel solution 9 for Top 4 FIFA WC Champions, proposed by El Badlis Mohd Marzudin:
=LET(
year,A2:A22,
champ,B2:B22,
champUnique,UNIQUE(champ),
champcount,COUNTIFS(champ,UNIQUE(champ)),
yearacc,BYROW(UNIQUE(champ),LAMBDA(a,TEXTJOIN(", ",TRUE,IF(champ=a,year,"")))),
final,SORTBY(HSTACK(champUnique,champcount,yearacc),champcount,-1),
FILTER(final,INDEX(final,,2)>=LARGE(INDEX(final,,2),4)))Excel solution 10 for Top 4 FIFA WC Champions, proposed by RIJESH T.:
=LET(u,UNIQUE(B2:B22),m,MAP(u,LAMBDA(a,LET(f,FILTER(A2:A22,B2:B22=a),a&"-"&COUNT(f)&"-"&ARRAYTOTEXT(f)))),t,TEXTSPLIT(TEXTJOIN(".",,m),"-","."),SORT(FILTER(t,--INDEX(t,,2)>1),{2,1},{-1,1}))Solving the challenge of Top 4 FIFA WC Champions with DAX
DAX solution 1 for Top 4 FIFA WC Champions, proposed by Zoran Milokanović:
EVALUATE
TOPN(
4,
SUMMARIZECOLUMNS(
Data[Champion],
"Times Won", COUNTROWS(Data),
"Years of Winning", CONCATENATEX(Data, Data[Year], ", ")
),
[Times Won], DESC
)
ORDER BY
[Times Won] DESC, Data[Champion]