repetition of a specific pattern! Identify the longest continuous repetition of the pattern “+ – -” and “+ -” in the question table for each product.
📌 Challenge Details and Links
Challenge Number: 9
Challenge Difficulty: ⭐⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find The Length Of The Largest with Power Query
Power Query solution 1 for Find The Length Of The Largest, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
GR = Table.Group(
Source,
{"Product", "Result"},
{"Count", each if Table.RowCount(_) > 1 then "1/1" else "1"},
0
),
UR = Table.Group(
GR,
{"Product"},
{
"Count",
each List.Max(List.Transform(Text.Split(Text.Combine(_[Count]), "/"), (x) => Text.Length(x)))
}
)
in
URPower Query solution 2 for Find The Length Of The Largest, proposed by Arden Nguyen, CPA:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"XY7LDYAwDMV26RUqAf0fSceouv8aQFQV8CEHyy+SWzOn3c1qzvsW09eHD7AbbAd7cMA+widwxr7A79tvIBoocyAaKB/v4D18gI/gBM74L2ANfB+qBtY5qBpYP97Be/gAH+ETfIYvYA0cD/0C",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Test ID (No)" = _t, Product = _t, Result = _t]
),
r1 = "+--",
r2 = "+-",
pattern = (t as table, p as text) =>
let
rows = Table.RowCount(t),
grouped_txt = Text.Combine(t[Result]),
rpt = Text.ToList(Text.Repeat(p, Number.RoundUp(rows / Text.Length(p), 0) + 1)),
max = List.Max(
List.Accumulate(
{0 .. Text.Length(p) - 1},
{},
(s, c) =>
s
& {
List.Max(
List.Generate(
() => [n = c, string = rpt{n}, l = Text.Length(string)],
each Text.Contains(grouped_txt, [string]),
each [n = [n] + 1, string = [string] & rpt{n}, l = Text.Length(string)],
each [l]
)
)
}
)
)
in
max,
a = Table.Group(
Source,
{"Product"},
{{"+--", each pattern(_, r1)}, {"+-", each pattern(_, r2)}},
GroupKind.Local,
(x, y) => Byte.From(x[Product] <> y[Product])
)
in
aPower Query solution 3 for Find The Length Of The Largest, proposed by Glyn Willis:
let
fxFind = (CharList, String) =>
let
lc = List.Count(CharList),
tl = List.Count(String)
in
List.Max(
Table.SelectRows(
Table.Group(
Table.FromList(
List.Generate(
() => [i = 0, a = String{i}, b = 0, c = a = CharList{b}, d = CharList{b}],
each [i] < tl,
each [
i = [i] + 1,
a = String{i},
b = Number.Mod(([b] + 1), lc),
c = a = CharList{b},
d = CharList{b}
],
each [c]
),
Splitter.SplitByNothing()
),
"Column1",
{{"Count", each Table.RowCount(_)}},
GroupKind.Local
),
each [Column1] = true
)[Count]
),
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Product"},
{
{"Ans1", each List.Max({fxFind({"+", "-"}, [Result]), fxFind({"-", "+"}, [Result])})},
{
"Ans2",
each List.Max(
{
fxFind({"+", "-", "-"}, [Result]),
fxFind({"-", "+", "-"}, [Result]),
fxFind({"-", "-", "+"}, [Result])
}
)
}
}
)
in
#"Grouped Rows"Solving the challenge of Find The Length Of The Largest with Excel
Excel solution 1 for Find The Length Of The Largest, proposed by Omid Motamedisedeh:
=>
=MAP(
UNIQUE(
C3:C32
),
LAMBDA(
mm,
LET(
z,
{"+--",
"--3",
"3+-",
"-3",
"3+",
"/4+"},
y,
{3,
"/5",
"5/",
"/4",
"4/",
"/5/"},
w,
REDUCE(
TEXTJOIN(
"",
,
FILTER(
D3:D32,
C3:C32=mm
)
),
{1,
2,
3,
4,
5,
6},
LAMBDA(
a,
b,
SUBSTITUTE(
a,
INDEX(
z,
b
),
INDEX(
y,
b
)
)
)
),
MAX(
SCAN(
0,
MID(
w,
SEQUENCE(
10
),
1
),
LAMBDA(
m,
n,
IFERROR(
m+n,
IFERROR(
--n,
0
)
)
)
)
)
)
)
)Excel solution 2 for Find The Length Of The Largest, proposed by Bo Rydobon 🇹🇭:
=LET(
p,
C3:C32,
r,
SUBSTITUTE(
$J$2,
" ", ),
l,
LEN(
r
), u,
UNIQUE(
p
),
MAP(
u,
LAMBDA(
a,
MAX(
LEN(
TEXTSPLIT(
REDUCE(
SUBSTITUTE(
CONCAT(
REPT(
D3:D32,
p=a
)
),
r,
REPT(
1,
l
)
),
l-SEQUENCE(
l-1
),
LAMBDA(
a,
i,
SUBSTITUTE(
SUBSTITUTE(
a,
1&LEFT(
r,
i
),
REPT(
1,
i+1
)&"x"
),
RIGHT(
r,
i
)&1,
"x"&REPT(
1,
i+1
)
)
)
),
{"+",
"-",
"x"}
)
)
)
)
)
)Excel solution 3 for Find The Length Of The Largest, proposed by محمد حلمي:
=MAP(
UNIQUE(
C3:C32
),
LAMBDA(
a,
LET(
d,
FILTER(
D3:D32,
C3:C32=a
),
s,
SEQUENCE(
20/2,
,
,
2
),
v,
LAMBDA(
a,
d,
a+d
),
MAX(
SCAN(
0,
MID(
CONCAT(
SORTBY(
d,
-SEQUENCE(
ROWS(
d
)
)
)
),
s,
2
)="+-",
v
),
SCAN(
0,
MID(
CONCAT(
d
),
s,
2
)="+-",
v
)
)*2
)
)
)Excel solution 4 for Find The Length Of The Largest, proposed by Kris Jaganah:
=MAP(UNIQUE(
C3:C32
),
LAMBDA(x,
LET(a,
FILTER(
D3:D32,
C3:C32=x
),
MAX(SCAN(0,
--(VSTACK(
DROP(
a,
1
),
TAKE(
a,
-1
)
)<>a),
LAMBDA(
x,
y,
IF(
y,
1+x,
0
)
)))+1)))Excel solution 5 for Find The Length Of The Largest, proposed by John Jairo Vergara Domínguez:
=MAP(
UNIQUE(
C3:C32
),
LAMBDA(
x,
LET(
b,
FILTER(
D3:D32,
C3:C32=x
),
MAX(
SCAN(
1,
DROP(
b,
-1
)&DROP(
b,
1
),
LAMBDA(
a,
v,
1+a*COUNT(
FIND(
v,
"+-+"
)
)
)
)
)
)
)
)
For "+--" (edited for little mistake):
✅=MAP(
UNIQUE(
C3:C32
),
LAMBDA(
x,
LET(
b,
FILTER(
D3:D32,
C3:C32=x
),
1+MAX(
SCAN(
1,
DROP(
b,
-2
)&DROP(
DROP(
b,
1
),
-1
)&DROP(
b,
2
),
LAMBDA(
a,
v,
1+a*COUNT(
FIND(
v,
"+--+-"
)
)
)
)
)
)
)
)Excel solution 6 for Find The Length Of The Largest, proposed by Charles Roldan:
=LET(Product,
$C$3:$C$32,
Result,
$D$3:$D$32,
Query,
G2,
f,
LAMBDA(Phrase,
LAMBDA(Message,
LET(n,
LEN(
Phrase
),LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA([a],
[y],IF(OR(
ISNUMBER(
FIND(
a,
Message
)
)
),g(
g
)(a & MID(
Phrase,
1 + MOD(
y - 1 + SEQUENCE(
n
),
n
),
1
),
1 + y),y - 1))))()))),
MAP(
MAP(
UNIQUE(
Product
), LAMBDA(
p,
CONCAT(
FILTER(
Result,
Product = p
)
)
)
), f(
SUBSTITUTE(
Query,
" ",
)
)
))Solving the challenge of Find The Length Of The Largest with R
R solution 1 for Find The Length Of The Largest, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("files/CH-009.xlsx", range = "B2:D32")
test1 = read_excel("files/CH-009.xlsx", range = "F2:G5") %>% janitor::clean_names()
test2 = read_excel("files/CH-009.xlsx", range = "I2:J5") %>% janitor::clean_names()
result = input %>%
group_by(Product) %>%
summarise(seq = str_c(Result, collapse = "")) %>%
ungroup()
to be continued...