— This week is FIFA world cup challenges — If a country has appeared more than once in finals, find the maximum gap in years for next – previous appearance for that country. For example, Italy reached into finals in 1934, 1938, 1970, 1982, 1994, 2006. Gap between these years are 1938-1934=4, 1970-1938=32, 1982-1970=12, 1994-1982=12, 2006-1994=12 Maximum gap here is 32 which is for years 1938-1970.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 29
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find max finals gap for with Power Query
Power Query solution 1 for Find max finals gap for, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
fnGetTable = (l) =>
let
C_1 = List.Skip(l, 1),
C_2 = List.RemoveLastN(l, 1),
C_3 = List.Transform({0 .. List.Count(l) - 2}, each C_1{_} - C_2{_}),
C_4 = List.Transform(
List.Zip({C_2, C_1}),
each Text.Combine(List.Transform(_, each Text.From(_)), "-")
),
C_5 = Table.FromRows(List.Zip({C_3, C_4}), {"Max Gap", "Years"}),
C_6 = Table.Group(C_5, {"Max Gap"}, {{"Years", each Text.Combine([Years], ", ")}}),
C_7 = Table.SelectRows(C_6, each ([Max Gap] = List.Max(C_6[Max Gap])))
in
C_7,
Unpivoted = Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Team"),
Grouped = Table.Group(
Unpivoted,
{"Team"},
{{"Count", each Table.RowCount(_)}, {"Year", each fnGetTable([Year])}}
),
Filtered = Table.SelectRows(Grouped, each [Count] > 1),
Expanded = Table.ExpandTableColumn(Filtered, "Year", {"Max Gap", "Years"}, {"Max Gap", "Years"}),
Removed = Table.RemoveColumns(Expanded, {"Count"}),
Sorted = Table.Sort(Removed, {{"Max Gap", Order.Descending}, {"Team", Order.Ascending}})
in
SortedPower Query solution 2 for Find max finals gap for, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
Part3:
#"Removed Other Columns2" = Table.SelectColumns(#"Filtered Rows2",{"Team", "Gap", "Years"}),
#"Grouped Rows2" = Table.Group(#"Removed Other Columns2", {"Team"}, {{"Year", each Text.Combine([Years],","), type nullable text}, {"All", each _, type table [Team=text, Gap=nullable number, Years=nullable text]}}),
#"Expanded All1" = Table.ExpandTableColumn(#"Grouped Rows2", "All", {"Gap"}, {"Gap"}),
#"Sorted Rows1" = Table.Sort(#"Expanded All1",{{"Gap", Order.Descending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows1", {"Team"})
in
#"Removed Duplicates"
Power Query solution 3 for Find max finals gap for, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
part2:
#"Added Custom1" = Table.AddColumn(#"Added Index", "NextYear", each if [Index]=0 then null else #"Added Index"[Year]{[Index.1]-1}),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each ([NextYear] <> null)),
#"Inserted Subtraction" = Table.AddColumn(#"Filtered Rows1", "Subtraction", each [Year] - [NextYear], type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Subtraction",{{"Subtraction", "Gap"}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Renamed Columns1", {{"Year", type text}, {"NextYear", type text}}, "en-US"),{"Year", "NextYear"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Years"),
#"Grouped Rows1" = Table.Group(#"Merged Columns", {"Team"}, {{"MaxGap", each List.Max([Gap]), type number}, {"All", each _, type table [Team=text, Index=number, Index.1=number, Years=text, Gap=number]}}),
#"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"Years", "Gap"}, {"Years", "Gap"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded All", "FinalYear", each if [MaxGap] = [Gap] then [Years] else null),
#"Filtered Rows2" = Table.SelectRows(#"Added Conditional Column", each ([FinalYear] <> null)),
Power Query solution 4 for Find max finals gap for, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Value"),
#"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Year", "Value"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Value", "Team"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Team", Order.Ascending}, {"Year", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Team"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Year=number, Team=text]}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] > 1),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.AddIndexColumn([All],"Index",0,1)),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns1", "Custom", {"Year", "Team", "Index"}, {"Year", "Team", "Index"}),
#"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 0, 1, Int64.Type),
Power Query solution 5 for Find max finals gap for, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "WCfinals"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Year"}, "Attribute", "Team")[
[Year],
[Team]
],
#"Grouped Rows" = Table.Group(
#"Unpivoted Columns",
{"Team"},
{
{
"All",
each
let
gap = Table.AddColumn(
Table.AddColumn(
Table.Skip(
Table.FromColumns(
{[Year]} & {{null} & List.RemoveLastN([Year], 1)},
{"Year", "Prev"}
),
1
),
"Max Gap",
each [Year] - [Prev]
),
"Years",
each Text.From([Prev]) & "-" & Text.From([Year])
)[[Max Gap], [Years]]
in
Table.SelectRows(gap, each [Max Gap] = List.Max(gap[Max Gap]))
}
}
),
#"Expanded All" = Table.ExpandTableColumn(
#"Grouped Rows",
"All",
{"Max Gap", "Years"},
{"Max Gap", "Years"}
),
#"Filtered Rows" = Table.SelectRows(#"Expanded All", each ([Years] <> null)),
#"Grouped Rows1" = Table.Group(
#"Filtered Rows",
{"Team", "Max Gap"},
{{"Years", each Text.Combine([Years], ", ")}}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows1", {{"Max Gap", Order.Descending}})
in
#"Sorted Rows"Power Query solution 6 for Find max finals gap for, proposed by Mahmoud Bani Asadi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Year"}, "Position", "Team"),
Grouped = Table.Group(
Unpivot,
{"Team"},
{{"Year", each _[Year]}, {"Next", each List.Skip(_[Year])}}
),
Custom = Table.AddColumn(
Grouped,
"Custom",
each Table.FromColumns({[Year], [Next]}, {"Year", "Next"})
)[[Team], [Custom]],
Expanded = Table.ExpandTableColumn(Custom, "Custom", {"Year", "Next"}, {"Year", "Next"}),
Subtract = Table.AddColumn(Expanded, "Gap", each [Next] - [Year], type number),
Gap = Table.SelectRows(Subtract, each ([Next] <> null)),
MaxGap = Table.Group(Gap, {"Team"}, {{"Gap", each List.Max([Gap])}}),
Combine = Table.Join(Gap, {"Team", "Gap"}, MaxGap, {"Team", "Gap"}),
Merged = Table.CombineColumns(
Table.TransformColumnTypes(Combine, {{"Year", type text}, {"Next", type text}}, "en-GB"),
{"Year", "Next"},
Combiner.CombineTextByDelimiter("-", QuoteStyle.None),
"Years"
),
Grp = Table.Group(Merged, {"Team", "Gap"}, {{"Years", each Text.Combine([Years], ",")}}),
Sorted = Table.Sort(Grp, {{"Gap", Order.Descending}})
in
SortedSolving the challenge of Find max finals gap for with Excel
Excel solution 1 for Find max finals gap for, proposed by محمد حلمي:
=LET(
a,
A2:A22,
w,
HSTACK(
VSTACK(
a,
a
),
TOCOL(
B2:C22,
,
1
)
),
u,
DROP(
w,
,
1
),
b,
SORT(REDUCE(HSTACK(
{"Team",
"Max Gap",
"Years"}
),
UNIQUE(
u
),
LAMBDA(a,
d,
LET(
v,
SORT(
FILTER(
w,
u=d
)
),
r,
TAKE(
v,
,
1
),
e,
DROP(
r,
1
),
n,
IFERROR(
e-DROP(
r,
-1
),
),
m,
MAX(
n
),
VSTACK(a,
HSTACK(d,
m,
TEXTJOIN(", ",
,
IFERROR(
IFNA(IF((m=n)*(r<>e),
r&"-"&e,
""),
""),
""))))))),
{2,
1},
{-1,
1}),
FILTER(
b,
LEN(
INDEX(
b,
,
2
)
)>1
))Excel solution 2 for Find max finals gap for, proposed by محمد حلمي:
=LET(
a,
A2:A22,
x,
VSTACK(
a,
a
),
r,
VSTACK(
B2:B22,
C2:C22
),
y,
UNIQUE(
r
),
o,
MAP(
y,
LAMBDA(
w,
LET(
z,
VSTACK(
3000,
SORT(
FILTER(
x,
r=w
)
)
),
c,
SCAN(
"",
SEQUENCE(
ROWS(
z
)
),
LAMBDA(
a,
d,
IFERROR(
INDEX(
z,
d+1
)-INDEX(
z,
d
),
)
)
),
v,
MAX(
c
)&" "&
SCAN(
"",
SEQUENCE(
ROWS(
c
)
),
LAMBDA(
a,
d,
TEXTJOIN(
", ",
,
FILTER(
MAP(
SEQUENCE(
ROWS(
z
)
),
LAMBDA(
a,
TEXTJOIN(
"-",
,
INDEX(
z,
a+{0;1}
)
)
)
),
c=MAX(
c
)
)
)
)
),
INDEX(
v,
COUNTA(
v
)
)
)
)
),
i,
SORT(
HSTACK(
y,
TEXTBEFORE(
o,
" "
)+0,
TEXTAFTER(
o,
" "
)
),
{2,
1},
{-1,
1}
),
FILTER(
i,
IFERROR(
INDEX(
i,
,
2
),
)
)
)Excel solution 3 for Find max finals gap for, proposed by 🇰🇷 Taeyong Shin:
=LET(
y,
A2:A22,
t,
B2:C22,
L,
LAMBDA(
n,
LAMBDA(
x,
LET(
s,
SORT(
x
),
e,
DROP(
VSTACK(
s,
0
),
1
),
d,
e-s,
m,
MAX(
d
),
IF(
n,
m,
ARRAYTOTEXT(
FILTER(
s&-e,
d=m
)
)
)
)
)
),
g,
GROUPBY(
TOCOL(
t,
,
1
),
VSTACK(
y,
y
),
HSTACK(
L(
1
),
L(
0
)
),
,
0,
-2,
TOCOL(
COUNTIF(
t,
t
)-1,
,
1
)
),
IF(
SEQUENCE(
ROWS(
g
)
)=1,
E1:G1,
g
)
)Excel solution 4 for Find max finals gap for, proposed by 🇰🇷 Taeyong Shin:
=LET(
Y, A2:A22,
W, B2:B22,
Ru, C2:C22,
Data, SORT(HSTACK(VSTACK(Y, Y), VSTACK(W, Ru)), {2,1}, {1,-1}),
Teams, CHOOSECOLS(Data, 2),
Wy, CHOOSECOLS(Data, 1),
Uteam, UNIQUE(VSTACK(UNIQUE(Teams), UNIQUE(Teams, , 1)), , 1),
Thunk, MAP(Uteam, LAMBDA(m, LAMBDA(
LET(
gap, FILTER(Wy, Teams = m),
mg, DROP(gap, -1) - DROP(gap, 1),
my, DROP(gap, 1) & "-" & DROP(gap, -1),
HSTACK(MAX(mg), ARRAYTOTEXT(SORT(FILTER(my, mg = MAX(mg)), , 1)) )
))
)),
Vals, DROP(REDUCE("", Thunk, LAMBDA(a,b, VSTACK(a, b()) )), 1),
SORT(HSTACK(Uteam, Vals), {2,1}, {-1,1})
)Excel solution 5 for Find max finals gap for, proposed by Bhavya Gupta:
=LET(Y,
A2:A22,
T,
B2:C22,
a,
TOCOL(
T
),
b,
TOCOL(
IFNA(
EXPAND(
Y,
,
2
),
Y
)
),
c,
UNIQUE(
a
),
d,
FILTER(c,
MAP(c,
LAMBDA(x,
SUM(--(x=a))))>1),
m,
MAP(
d,
LAMBDA(
x,
LET(
p,
FILTER(
b,
a=x
),
q,
DROP(
p,
1
),
r,
DROP(
p,
-1
),
s,
q-r,
ARRAYTOTEXT(
FILTER(
r&"-"&q,
s=MAX(
s
)
)
)&";"&MAX(
s
)
)
)
),
s,
SORT(
HSTACK(
d,
TEXTAFTER(
m,
";"
)*1,
TEXTBEFORE(
m,
";"
)
),
{2,
1},
{-1,
1}
),
s)Excel solution 6 for Find max finals gap for, proposed by Rajesh Sinha:
=TRANSPOSE(
UNIQUE(
SORT(
FILTER(
$B$50:$B$68,
COUNTIFS(
$B$50:$B$68,
$B$50:$B$68
)>=4,
""
),
,
1
)
& )
)
D54: =FILTER(
$A$50:$A$68,
$B$50:$B$68=D$53,
""
)
G54: =IF(
ISBLANK(
D55
),
"",
D55-D54
)
G61: =SUM(
G54:G59
)Solving the challenge of Find max finals gap for with SQL
SQL solution 1 for Find max finals gap for, proposed by Zoran Milokanović:
SELECT
F.TEAM
, F.GAP AS MAX_GAP
,LISTAGG(F.YEAR) AS YEAR
FROM
(
SELECT
T2.TEAM
,T2.YEAR - T2.PREVIOUS_APPEARANCE AS GAP
,TO_CHAR(T2.PREVIOUS_APPEARANCE) || '-' || TO_CHAR(T2.YEAR) AS YEAR
,RANK() OVER (PARTITION BY T2.TEAM ORDER BY T2.YEAR - T2.PREVIOUS_APPEARANCE DESC) AS TOP_N_WITH_TIES
FROM
(
SELECT
T.YEAR
, T.TEAM
,LAG(T.YEAR) OVER (PARTITION BY T.TEAM ORDER BY T.YEAR) AS PREVIOUS_APPEARANCE
FROM
(
SELECT
D.YEAR
, D.WINNERS AS TEAM
FROM DATA D
UNION ALL
SELECT
D.YEAR
,D.RUNNERS_UP AS TEAM
FROM DATA D
) T
) T2
WHERE
T2.PREVIOUS_APPEARANCE IS NOT NULL
) F
WHERE
F.TOP_N_WITH_TIES = 1 -- MAXIMUM GAP
GROUP BY
F.TEAM
, F.GAP
ORDER BY
2 DESC, 1
;
&&