List the names who have won consecutively at least 3 times within a span of 180 days. Span of 180 days is last win date – first win date in a consecutive streak of wins. 2022-01-15 Y 2022-01-28 N 2022-02-12 Y 2022-03-18 Y 2022-05-12 Y Here all 3 consecutive wins are within 180 days 2022-05-12 – 2022-02-12 : 89 days (The winning streak of at least 3 consecutive wins start on 2022-02-12 not on 2022-01-15) 2022-01-15 Y 2022-01-28 Y 2022-02-12 N 2022-03-18 Y 2022-05-12 Y Here, 2022-02-12 N – Breaks at least 3 consecutive criterion If you may have more than one consecutive streaks of at least 3 consecutive wins, in this case, list any one of them. 2022-01-15 Y 2022-01-28 Y 2022-01-31 Y 2022-02-12 N 2022-03-18 Y 2022-05-12 Y 2022-06-08 Y 2022-06-28 Y Here, there are 2 streaks of consecutive wins Streak 1 2022-01-15 Y 2022-01-28 Y 2022-01-31 Y Streak 2 2022-03-18 Y 2022-05-12 Y 2022-06-08 Y 2022-06-28 Y List either of them. My preference would be Streak 1.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 21
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Consecutive Wins Within Span with Power Query
Power Query solution 1 for Consecutive Wins Within Span, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(Source, {"Name"}, {{"all", each Table.Sort(_, each Date.From([Date]))}})[
all
],
CreateLists = List.Transform(
Grouped,
(a) =>
List.Accumulate(
Table.ToRecords(a),
{[d = null, r = null]},
(state, current) =>
if List.Last(state)[r] = null and current[Won] = "Y" then
state & {[d = current[Date], r = {current}]}
else if current[Won]
= "Y" and Duration.Days(Date.From(current[Date]) - Date.From(List.Last(state)[d]))
<= 180
then
List.RemoveLastN(state, 1)
& {[d = List.Last(state)[d], r = List.Last(state)[r] & {current}]}
else
state & {[d = null, r = null]}
)
),
Result = Table.FromRecords(
List.Combine(
Table.FromRecords(
List.RemoveNulls(
List.Transform(
CreateLists,
each List.Select(_, (b) => b[d] <> null and List.Count(b[r]) >= 3){0}?
)
)
)[r]
)
)[[Name], [Date]]
in
ResultPower Query solution 2 for Consecutive Wins Within Span, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
GroupedLocal = Table.SelectRows(
Table.Group(
Source,
{"Name", "Won"},
{
{
"Date",
each
if (Table.RowCount(_) >= 3 and [Won]{0} = "Y") then
[
d = List.Transform([Date], Date.From),
n = List.Union(
List.RemoveNulls(
List.Transform(
{0 .. List.Count(d) - 3},
each if Number.From(d{_ + 2} - d{_}) <= 180 then {_ .. _ + 2} else null
)
)
),
r = List.Transform(n, each d{_})
][r]
else
false
}
},
GroupKind.Local
),
each ([Date] <> false)
)[[Name], [Date]],
ExpandDate = Table.ExpandListColumn(GroupedLocal, "Date")
in
ExpandDatePower Query solution 3 for Consecutive Wins Within Span, proposed by Abdoul Karim N.:
let
Source = Excel.CurrentWorkbook(){[Name = "Plays"]}[Content],
ChangedType = Table.TransformColumnTypes(
Source,
{{"Name", type text}, {"Date", type date}, {"Won", type text}}
),
SortedRows = Table.Sort(ChangedType, {{"Name", Order.Ascending}, {"Date", Order.Ascending}}),
GroupRows = Table.Group(
SortedRows,
{"Name", "Won"},
{
{
"Count",
each _,
type table [Name = nullable text, Date = nullable date, Won = nullable text]
},
{"Wins-Loss", each Table.RowCount(_), Int64.Type}
}
),
OnlyWins = Table.SelectRows(GroupRows, each ([Won] = "Y" and [#"Wins-Loss"] >= 3)),
Days = Table.AddColumn(
OnlyWins,
"Days",
each Number.From(
List.Max(Table.Column([Count], "Date")) - List.Min(Table.Column([Count], "Date"))
)
),
FilterDays = Table.SelectRows(Days, each [Days] <= 180),
RemovedOtherColumns = Table.SelectColumns(FilterDays, {"Count"}),
FinalResult = Table.ExpandTableColumn(
RemovedOtherColumns,
"Count",
{"Name", "Date"},
{"Name", "Date"}
)
in
FinalResultPower Query solution 4 for Consecutive Wins Within Span, proposed by Guido Hendrickx:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Name", type text}, {"Date", type date}, {"Won", type text}}
),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"Name", "Won"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"Sub", each _, type table [Name = nullable text, Date = nullable date, Won = nullable text]}
},
GroupKind.Local
),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Count] >= 3),
#"Added Custom" = Table.AddColumn(
#"Filtered Rows",
"Has 3 Strikes",
each Text.Contains(List.Accumulate([Sub][Won], "", (r, v) => r & v), "YYY")
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Duration",
each Duration.Days(List.Max([Sub][Date]) - List.Min([Sub][Date]))
),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom1", each [Duration] <= 180),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows1", {"Name"}),
#"Removed Other Columns" = Table.SelectColumns(#"Removed Duplicates", {"Name", "Sub"}),
#"Expanded Sub" = Table.ExpandTableColumn(#"Removed Other Columns", "Sub", {"Date"}, {"Date"})
in
#"Expanded Sub"Solving the challenge of Consecutive Wins Within Span with Excel
Excel solution 1 for Consecutive Wins Within Span, proposed by Bo Rydobon 🇹🇭:
=LET(z,
SORT(
A2:C17,
{1,
2}
),
n,
TAKE(
z,
,
1
),
d,
INDEX(
z,
,
2
),
w,
TAKE(
z,
,
-1
),
y,
(w="Y"),
c,
y*(d-XLOOKUP(
n&"Y",
n&w,
d
)<=180),
FILTER(
TAKE(
z,
,
2
),
y*ISNUMBER(
FIND(
111,
MAP(
n,
c,
LAMBDA(
a,
b,
CONCAT(
FILTER(
c,
a=n
)
)
)
)
)
)
))Excel solution 2 for Consecutive Wins Within Span, proposed by Bo Rydobon 🇹🇭:
=LET(z,
SORT(
A2:C20,
{1,
2}
),
n,
TAKE(
z,
,
1
),
d,
INDEX(
z,
,
2
),
w,
TAKE(
z,
,
-1
),
y,
N(
w="Y"
),
s,
FIND(
111,
MAP(
n,
y,
LAMBDA(
a,
b,
CONCAT(
FILTER(
y,
a=n
)
)
)
)
),
FILTER(TAKE(
z,
,
2
),
IFERROR((MAP(
n,
s,
LAMBDA(
a,
c,
LET(
x,
INDEX(
FILTER(
d,
n=a
),
c
),
x
)
)
)<=d)*y,
)))Excel solution 3 for Consecutive Wins Within Span, proposed by محمد حلمي:
=REDUCE(A1:B1,
C2:C17,
LAMBDA(a,
d,
IF(AND(
"Y"=OFFSET(
d,
,
,
3
),
OFFSET(
d,
,
-2
)=OFFSET(
d,
,
-2,
3
),
(OFFSET(
d,
2,
-1
)-OFFSET(
d,
,
-1
)<181)),
UNIQUE(
VSTACK(
a,
OFFSET(
d,
,
-2,
3,
2
)
)
),
a)))Excel solution 4 for Consecutive Wins Within Span, proposed by محمد حلمي:
=LET(
b,
A2:C17,
i,
{0;1;2},
k,
SEQUENCE(
ROWS(
b
)
),
e,
MAP(
k,
LAMBDA(
d,
LET(
v,
CHOOSEROWS(
b,
d+i
),
r,
INDEX(
v,
,
2
),
AND(
INDEX(
v,
1,
1
)=INDEX(
v,
,
1
),
INDEX(
r,
3
)-INDEX(
r,
1
)<181,
INDEX(
v,
,
3
)="Y"
)
)
)
),
VSTACK(
A1:B1,
FILTER(
TAKE(
b,
,
2
),
MAP(
k,
LAMBDA(
d,
OR(
IFERROR(
INDEX(
e,
d-i
),
)
)
)
)
)
)
)Excel solution 5 for Consecutive Wins Within Span, proposed by محمد حلمي:
=LET(c,
A2:A17,
y,
(C2:C17="y"),
FILTER(A2:B17,
MAP(c,
LAMBDA(a,
LET(a,
FILTER(B2:B17,
(c=a)*y)+0,
(ROWS(
a
)>2)*(MAX(
a
)-MIN(
a
)<180))))*y))Excel solution 6 for Consecutive Wins Within Span, proposed by 🇰🇷 Taeyong Shin:
=LET(name,
M2:M17,
Dt,
--N2:N17,
Won,
O2:O17,
Uname,
UNIQUE(
name
),
Bool,
MAP(Uname,
LAMBDA(m,
LET(wList,
FILTER(
Won,
name=m
),
dtList,
FILTER(Dt,
(name=m)*(Won="Y")),
IF(
ISNUMBER(
SEARCH(
"Y, Y, Y",
ARRAYTOTEXT(
wList
)
)
),
DAYS(
SMALL(
dtList,
3
)+1,
MIN(
dtList
)
)<=180
)
)
)),
Result,
REDUCE("",
FILTER(
Uname,
Bool
),
LAMBDA(a,
b,
VSTACK(a,
FILTER(HSTACK(
name,
Dt
),
(name=b)*(Won="Y")) )
)),
SORT(
DROP(
Result,
1
),
{1,
2}
)
)Excel solution 7 for Consecutive Wins Within Span, proposed by 🇰🇷 Taeyong Shin:
=LET(name,
A2:A17,
Dt,
--B2:B17,
Won,
C2:C17,
Uname,
UNIQUE(
name
),
Bool,
MAP(Uname,
LAMBDA(m,
LET(List,
FILTER(Dt,
(name=m)*(Won="Y")),
(ROWS(
List
)>=3)*(DAYS(
MAX(
List
)+1,
MIN(
List
)
)<=180)
)
)),
List,
ARRAYTOTEXT(
FILTER(
Uname,
Bool
)
),
SORT(FILTER(HSTACK(
name,
Dt
),
ISNUMBER(
SEARCH(
name,
List
)
)*(Won="Y") ),
{1,
2})
)Excel solution 8 for Consecutive Wins Within Span, proposed by Bhavya Gupta:
=LET(N,
B3:B18,
D,
C3:C18,
W,
D3:D18,
Wins_Cons,
3,
Comb,
HSTACK(
N,
D,
W
),
a,
SCAN(
0,
VSTACK(
FALSE,
DROP(
N,
1
)=DROP(
N,
-1
)
)*VSTACK(
FALSE,
DROP(
W,
1
)=DROP(
W,
-1
)
),
LAMBDA(
x,
y,
IF(
y,
x,
x+1
)
)
),
f,
FILTER(
a,
W="Y"
),
u,
UNIQUE(
f
),
R,
FILTER(
u,
DROP(
FREQUENCY(
f,
u
),
-1
)>=Wins_Cons
),
My,
DROP(REDUCE(0,
R,
LAMBDA(o,
p,
VSTACK(o,
LET(t,
FILTER(
Comb,
a=p
),
i,
ROWS(
t
),
q,
CHOOSECOLS(
t,
2
),
k,
EXPAND(
DROP(
q,
Wins_Cons-1
),
i
)-q,
s,
SEQUENCE(
i
),
FILTER(t,
REDUCE(0,
s,
LAMBDA(x,
y,
LET(h,
INDEX(
k,
y
),
x+IFNA(IF(h<181,
(s>=y)*(sExcel solution 9 for Consecutive Wins Within Span, proposed by Stefan Olsson:
=QUERY(
{A2:C17},
"Select Col1, Col2 where Col3='Y' and Col1 matches '"&JOIN(
"|",
QUERY(
{QUERY(
{A2:C17},
"select Col1, max(Col2), min(Col2), count(Col3), datediff(max(Col2),min(Col2)) where Col3='Y' group by Col1",
0
)},
"select Col1 where Col4>2 and Col5<=180",
0
)
)&"' order by Col1, Col2 label Col1 'Name', Col2 'Date'"
)