— This week will be FIFA World Cup week. All challenges will be related to FIFA World Cup only for this week. — List the champions if they have re-won the world cup within 20 years of last winning the world cup. Hence, West Germany would be 1954, 1974, 1990 as first time they won in 1954 and within 20 years in 1974 they won again. Then within 20 years from 1974, they won again in 1990. 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: 70
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Repeat Winners within 20 Years with Power Query
Power Query solution 1 for Repeat Winners within 20 Years, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "WorldCupWins"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Champion"},
{
{
"All",
each Table.FromColumns({[Year]} & {{null} & List.RemoveLastN([Year], 1)}, {"Year", "prev"})
}
}
),
#"Expanded All" = Table.ExpandTableColumn(
#"Grouped Rows",
"All",
{"Year", "prev"},
{"Year", "prev"}
),
#"Added Diff" = Table.AddColumn(
#"Expanded All",
"Custom",
each if [prev] = null then 0 else if [Year] - [prev] > 20 then 0 else [Year] - [prev]
),
#"Added StreakName" = Table.AddColumn(
#"Added Diff",
"Streak",
each if [Custom] = 0 then [Champion] & " " & Text.From([Year]) else null
),
#"Filled Down" = Table.FillDown(#"Added StreakName", {"Streak"}),
#"Grouped Rows1" = Table.Group(
#"Filled Down",
{"Streak", "Champion"},
{{"Year", each Text.Combine(List.Transform([Year], each Text.From(_)), ", ")}}
)[[Champion], [Year]],
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows1", each Text.Contains([Year], ",")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows", {{"Year", Order.Ascending}})
in
#"Sorted Rows"Power Query solution 2 for Repeat Winners within 20 Years, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Champion"},
{
{
"Year",
each List.Transform(
List.Select(
List.Accumulate(
List.Skip([Year]),
[myList = {{Text.From([Year]{0})}}, lastYear = [Year]{0}],
(state, current) =>
if state[lastYear] + 20 >= current then
[
myList = List.RemoveLastN(state[myList])
& {List.Last(state[myList]) & {Text.From(current)}},
lastYear = current
]
else
[myList = state[myList] & {{Text.From(current)}}, lastYear = current]
)[myList],
each List.Count(_) > 1
),
each Text.Combine(_, ", ")
)
}
}
),
Filter = Table.SelectRows(Group, each not List.IsEmpty([Year])),
Expand = Table.ExpandListColumn(Filter, "Year"),
Sort = Table.Sort(Expand, {{"Year", Order.Ascending}})
in
SortPower Query solution 3 for Repeat Winners within 20 Years, proposed by Venkata Rajesh:
let
Source = Data,
Sorted = Table.Sort(Source, {{"Champion", Order.Ascending}, {"Year", Order.Ascending}}),
Magic = Table.AddColumn(
Sorted,
"Group",
each
let
_row = Table.PositionOf(Sorted, _),
_Check =
if try Sorted{_row + 1}[Champion] = [Champion] otherwise false then
null
else
[Champion],
_Prv = Sorted{_row - 1}[Year],
_Nxt = Sorted{_row + 1}[Year]
in
if _Check <> null and [Year] - _Prv > 20 then
null
else if _Check <> null and [Year] - _Prv <= 20 then
[Year]
else if _Nxt - [Year] > 20 and [Year] - _Prv > 20 then
null
else if _Nxt - [Year] > 20 then
[Year]
else
null
),
#"Grouped Rows" = Table.Group(
Magic,
{"Champion"},
{{"Count", each Table.RowCount(_), Int64.Type}, {"AllRows", each Table.FillUp(_, {"Group"})}}
),
#"Expanded AllRows" = Table.ExpandTableColumn(
#"Grouped Rows",
"AllRows",
{"Year", "Group"},
{"Year", "Group"}
),
#"Filtered Rows" = Table.SelectRows(#"Expanded AllRows", each ([Group] <> null and [Count] > 1)),
#"Grouped Rows1" = Table.Group(
#"Filtered Rows",
{"Champion", "Group"},
{{"Years", each Text.Combine(List.Transform([Year], each Text.From(_)), ", ")}}
)[[Champion], [Years]],
#"Sorted Rows" = Table.Sort(#"Grouped Rows1", {{"Years", Order.Ascending}})
in
#"Sorted Rows"Power Query solution 4 for Repeat Winners within 20 Years, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Tabel1"]}[Content],
ChType = Table.TransformColumnTypes(Source, {{"Year", type text}, {"Champion", type text}}),
GroupRows = Table.Group(
ChType,
{"Champion"},
{
{
"t",
each [
a = List.RemoveLastN(List.Zip({[Year], List.Skip([Year], 1)}), 1),
b = List.Transform(a, each if Number.From(_{1}) - Number.From(_{0}) <= 20 then _ else {}),
c = if List.Count(b) > 1 then List.Count(b) - 1 else 1,
d = List.PositionOf(List.Transform(b, List.Count), 0, Occurrence.All),
e = List.Transform(
List.Zip({{0} & d, d}),
each Text.Combine(List.Union(List.Range(b, _{0}, _{1} ?? c)), ", ")
)
][e]
}
}
),
ExpandLists = Table.SelectRows(Table.ExpandListColumn(GroupRows, "t"), each ([t] <> ""))
in
ExpandListsPower Query solution 5 for Repeat Winners within 20 Years, proposed by Bohdan Duda, PhD:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Champion"},
{{"Year_", each _, type table [Year = number]}}
),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Year", each [Year_][Year]),
#"Extracted Values" = Table.TransformColumns(
#"Added Custom",
{"Year", each Text.Combine(List.Transform(_, Text.From), ", "), type text}
),
#"Removed Other Columns" = Table.SelectColumns(#"Extracted Values", {"Champion", "Year"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns", {{"Champion", type text}})
in
#"Changed Type"Solving the challenge of Repeat Winners within 20 Years with Excel
Excel solution 1 for Repeat Winners within 20 Years, proposed by John V.:
=LET(c,REDUCE("",UNIQUE(B2:B22),LAMBDA(i,a,VSTACK(i,LET(b,FILTER(A2:B22,B2:B22=a),
y,TAKE(b,,1),
z,SCAN("",SEQUENCE(ROWS(b)),LAMBDA(i,x,IF(INDEX(y-VSTACK(0,DROP(y,-1)),x)>20,"",i&", ")&INDEX(y,x))),
c,VSTACK(DROP(z,1),0),
IFERROR(FILTER(HSTACK(DROP(b,,1),z),(LEN(z)>4)*(LEN(c)<5)),""))))),
e,FILTER(c,TAKE(c,,1)>""),
SORTBY(e,LEFT(DROP(e,,1),4)))Excel solution 2 for Repeat Winners within 20 Years, proposed by 🇰🇷 Taeyong Shin:
=LET(s,SORT(A2:B22,{2,1}),y,TAKE(s,,1),c,DROP(s,,1),d,DROP(y,1)-DROP(y,-1),n,SCAN(,VSTACK(0,(DROP(c,1)=DROP(c,-1))*(d<=20)),LAMBDA(a,v,IF(v,a,a+1))),DROP(GROUPBY(HSTACK(n,c),y,ARRAYTOTEXT,,0,3,MMULT(XMATCH(n,n,,{1,-1}),{-1;1})),,1))Excel solution 3 for Repeat Winners within 20 Years, proposed by Julian Poeltl:
=LET(Ye,
A2:A22,
W,
B2:B22,
R,
HSTACK(W,
MAP(Ye,
W,
LAMBDA(Y,
C,
LET(F,
FILTER(HSTACK(
Ye,
W
),
(Ye<=Y+20)*(Ye>=Y)),
TEXTJOIN(
", ",
,
FILTER(
TAKE(
F,
,
1
),
TAKE(
F,
,
-1
)=C
)
))))),
T,
FILTER(
R,
LEN(
TAKE(
R,
,
-1
)
)>4
),
A,
FILTER(
T,
NOT(
ISNUMBER(
SEARCH(
TAKE(
T,
,
-1
),
DROP(
VSTACK(
"",
SCAN(
,
TAKE(
T,
,
-1
),
LAMBDA(
A,
B,
CONCAT(
A,
B
)
)
)
),
-1
)
)
)
)
),
B,
SORT(
A
),
BB,
TAKE(
B,
,
-1
),
C,
MAP(
BB,
LAMBDA(
A,
TAKE(
TEXTSPLIT(
A,
", "
),
,
-1
)
)
),
D,
DROP(
TEXTSPLIT(
BB,
", "
),
1
),
F,
IFERROR((TAKE(
B,
,
1
)=DROP(
TAKE(
B,
,
1
),
1
))*(C>=D),
0),
G,
IFNA(
TEXTAFTER(
IF(
F,
DROP(
BB,
1
),
""
),
", "
),
""
),
H,
IF(
G<>"",
BB&", "&G,
BB
),
I,
FILTER(
H,
NOT(
ISNUMBER(
SEARCH(
H,
DROP(
VSTACK(
"",
SCAN(
,
H,
LAMBDA(
A,
B,
CONCAT(
A,
B
)
)
)
),
-1
)
)
)
)
),
SORT(
HSTACK(
XLOOKUP(
--TEXTSPLIT(
I,
","
),
Ye,
W
),
I
),
2
))Excel solution 4 for Repeat Winners within 20 Years, proposed by Timothée BLIOT:
=LET(A,
A2:A22,
B,
B2:B22,
U,
UNIQUE(
B2:B22
),
V,
IFERROR(1*(TEXTSPLIT(
TEXTJOIN(
"/",
,
MAP(
U,
LAMBDA(
x,
TEXTJOIN(
",",
,
FILTER(
A,
B=x
)
)
)
)
),
",",
"/",
1,
,
""
)),
""),
S,
BYROW(
V,
LAMBDA(
x,
COUNT(
x
)
)
),
T,
FILTER(
V,
S>1
),
K,
MAKEARRAY(ROWS(
T
),
COLUMNS(
T
),
LAMBDA(x,
y,
IF(y=1,
1,
IF(INDEX(
T,
x,
y
)="",
"",
--(INDEX(
T,
x,
y
)-INDEX(
T,
x,
y-1
)<=20))))),
L,
IFERROR(1*(TEXTSPLIT(
TEXTJOIN(
"/",
,
BYROW(
K,
LAMBDA(
a,
TEXTJOIN(
",",
,
SCAN(
1,
a,
LAMBDA(
a,
v,
IF(
v=1,
a,
IF(
v="",
"",
a+1
)
)
)
)
)
)
)
),
",",
"/"
)),
""),
M,
1*(TEXTSPLIT(TEXTJOIN("/",
,
BYROW(L,
LAMBDA(r,
TEXTJOIN(",",
,
MAP(r,
LAMBDA(x,
IF(SUMPRODUCT(1*(x=FILTER(
r,
r<>""
)))>1,
1,
0))))))),
",",
"/")),
N,
MAKEARRAY(
ROWS(
M
),
COLUMNS(
M
),
LAMBDA(
x,
y,
IF(
INDEX(
M,
x,
y
)=1,
INDEX(
L,
x,
y
),
""
)
)
),
R,
MAKEARRAY(
ROWS(
M
),
COLUMNS(
M
),
LAMBDA(
x,
y,
IF(
INDEX(
M,
x,
y
)=1,
INDEX(
T,
x,
y
),
""
)
)
),
O,
HSTACK(
FILTER(
TOCOL(
R
),
TOCOL(
R
)<>""
),
FILTER(
TOCOL(
N
),
TOCOL(
N
)<>""
)
),
F,
LAMBDA(Self,
n,
out,
IF(n>=(MAX(
INDEX(
O,
,
2
)
)+1),
DROP(
out,
,
1
),
Self(
Self,
n+1,
HSTACK(
out,
FILTER(
INDEX(
O,
,
1
),
INDEX(
O,
,
2
)=n
)
)
))),
G,
IFERROR(
F(
F,
MIN(
INDEX(
O,
,
2
)
),
""
),
""
),
H,
LAMBDA(
Self,
Num,
Out,
IF(
Num>COLUMNS(
G
),
DROP(
Out,
1
),
Self(
Self,
Num+1,
VSTACK(
Out,
LET(
Q,
BYROW(
INDEX(
G,
,
Num
),
LAMBDA(
x,
XLOOKUP(
x,
A,
B,
""
)
)
),
HSTACK(
UNIQUE(
Q
),
BYROW(
UNIQUE(
Q
),
LAMBDA(
x,
TEXTJOIN(
",",
,
FILTER(
INDEX(
G,
,
Num
),
Q=x
)
)
)
)
)
)
)
)
)
),
J,
H(
H,
1,
""
),
SORT(
FILTER(
J,
INDEX(
J,
,
& 1
)<>""
),
2
))Excel solution 5 for Repeat Winners within 20 Years, proposed by Bhavya Gupta:
=LET(Y,
A2:A22,
C,
B2:B22,
S,
SORTBY(
HSTACK(
Y,
C
),
C,
,
Y,
),
Yf,
TAKE(
S,
,
1
),
Cf,
TAKE(
S,
,
-1
),
J,
DROP(
Yf,
1
)-DROP(
Yf,
-1
),
K,
SCAN(0,
VSTACK(0,
(J>0)*(J<21)*(DROP(
Cf,
1
)=DROP(
Cf,
-1
))),
LAMBDA(
x,
y,
IF(
y,
x,
x+1
)
)),
B,
SORTBY(
K,
Yf
),
U,
UNIQUE(
VSTACK(
UNIQUE(
B
),
UNIQUE(
B,
,
1
)
),
,
1
),
X,
XLOOKUP(
U,
K,
Cf
),
HSTACK(
X,
MAP(
U,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
Yf,
K=a
)
)
)
)
))Solving the challenge of Repeat Winners within 20 Years with Python in Excel
Python in Excel solution 1 for Repeat Winners within 20 Years, proposed by Alejandro Campos:
data = xl("A1:B22", headers=True)
champions_years = {}
for index, row in data.iterrows():
champion = row['Champion']
year = row['Year']
if champion not in champions_years:
champions_years[champion] = []
champions_years[champion].append(year)
results = []
for champion, years in champions_years.items():
years.sort()
consecutive_wins = [years[0]]
for i in range(1, len(years)):
if years[i] - consecutive_wins[-1] <= 20:
consecutive_wins.append(years[i])
else:
if len(consecutive_wins) > 1:
results.append({"Champion": champion, "Year": ", ".join(map(str, consecutive_wins))})
consecutive_wins = [years[i]]
if len(consecutive_wins) > 1:
results.append({"Champion": champion, "Year": ", ".join(map(str, consecutive_wins))})
results_df = pd.DataFrame(results)
results_df
Solving the challenge of Repeat Winners within 20 Years with DAX
DAX solution 1 for Repeat Winners within 20 Years, proposed by Zoran Milokanović:
DEFINE
VAR P = FILTER(Input,
VAR CY = Input[Year]
VAR PY = SELECTCOLUMNS(OFFSET(-1, ORDERBY(Input[Year]), PARTITIONBY(Input[Champion])), Input[Year])
VAR NY = SELECTCOLUMNS(OFFSET(+1, ORDERBY(Input[Year]), PARTITIONBY(Input[Champion])), Input[Year])
RETURN OR(CY - PY < 21, ABS(NY - CY) < 21 )
)
EVALUATE
SELECTCOLUMNS(
ADDCOLUMNS(
GROUPBY(
ADDCOLUMNS(
P, "ID",
SUMX(
ADDCOLUMNS(
WINDOW(1, ABS, 0, REL, ORDERBY(Input[Year]), PARTITIONBY(Input[Champion])),
"T", IF(Input[Year] - SELECTCOLUMNS(OFFSET(-1, ORDERBY(Input[Year]), PARTITIONBY(Input[Champion])), Input[Year]) > 20, 1, 0)
), [T]
)
),
Input[Champion], [ID], "@MIN", MINX(CURRENTGROUP(), Input[Year]), "@MAX", MAXX(CURRENTGROUP(), Input[Year])
),
"Y", VAR C = Input[Champion] RETURN CONCATENATEX(FILTER(P, Input[Champion] = C && Input[Year] >= [@MIN] && Input[Year] <= [@MAX]), Input[Year], ", ")
),
Input[Champion], "Year", [Y]
)
ORDER BY
[Year]
Solving the challenge of Repeat Winners within 20 Years with SQL
SQL solution 1 for Repeat Winners within 20 Years, proposed by Zoran Milokanović:
SELECT
F.CHAMPION
,LISTAGG(DISTINCT F.YEAR) AS YEAR
FROM
(
SELECT
T.YEAR
,T.CHAMPION
FROM
(
SELECT
D.YEAR
,D.CHAMPION
,NVL(LAG(D.YEAR) OVER (PARTITION BY D.CHAMPION ORDER BY D.YEAR), D.YEAR) AS PREVIOUS_YEAR
FROM DATA D
WHERE
) T
ORDER BY
2, 3, 1
) F
GROUP BY
F.CHAMPION
,F.GROUP_ID
ORDER BY
2
;