_x000D_
Excel solution 16 for Pattern Length!, proposed by Md. Zohurul Islam:
=LET( u,
B3:B7, v,
C3:C7, hdr,
{"Date",
"Length"}, w,
MAP(
v,
LAMBDA(
p,
LET(
a,
DROP(
TEXTSPLIT(
p,
,
" "
),
-1
),
b,
VSTACK(
0,
ABS(
DROP(
a,
1
)=DROP(
a,
-1
)For each date, samples are evaluated based on quality and marked with a “+” sign if they are within range and a “-” sign if they are out of range. Extract the longest sequence of consecutive “+” or “-” signs for each date.
📌 Challenge Details and Links
Challenge Number: 194
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pattern Length! with Power Query
_x000D_
Power Query solution 1 for Pattern Length!, proposed by Brian Julius:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{"Date", Date.Type}
),
AddSymbols = Table.SelectRows(
Table.ExpandListColumn(
Table.AddColumn(Source, "Symbols", each Text.ToList([Pattern])),
"Symbols"
),
each [Symbols] <> " "
),
Group = Table.Group(
AddSymbols,
{"Date", "Symbols"},
{{"Len", each Table.RowCount(_)}},
GroupKind.Local
),
Group2 = Table.Group(
Group,
{"Date"},
{{"MaxCount", each List.Max([Len]), type number}, {"All", each _}}
),
ExpFilt = Table.RemoveColumns(
Table.SelectRows(
Table.ExpandTableColumn(Group2, "All", {"Symbols", "Len"}, {"Symbols", "Len"}),
each [MaxCount] = [Len]
),
"MaxCount"
),
Clean = Table.RemoveColumns(
Table.AddColumn(ExpFilt, "Length", each [Symbols] & Text.From([Len])),
{"Symbols", "Len"}
)
in
Clean
_x000D_
_x000D_
Power Query solution 2 for Pattern Length!, proposed by Eric Laforce:
letter
2) Add new col [Length] using it only for characters "+-' in [Pattern]
+ find the one with Max Text.Length
3) Project to keep only cols [Date], [Length] as final result
let
fxTextSplitByConsecutiveLetters = (t as text) =>List.Accumulate( Text.ToList(t), {}, (s,c)=>let
PrevChar = try Text.Start(List.Last(s),1) otherwise ""
in if (c=PrevChar) then List.RemoveLastN(s) & {List.Last(s) & c} else s & {c} ),
Source = Excel.CurrentWorkbook(){[Name="tData"]}[Content],
AddLength = Table.AddColumn(Source, "Length", each let
_l = fxTextSplitByConsecutiveLetters(Text.Select([Pattern], {"+","-"})),
_Max = List.Max(_l, "", (x,y)=>Byte.From(Text.Length(x)>Text.Length(y)))
in Text.Start(_Max,1) & Text.From(Text.Length(_Max))
)
in
AddLength[[Date],[Length]]
_x000D_
_x000D_
Power Query solution 3 for Pattern Length!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
LT = List.Transform,
a = Table.ToRows(S),
b = LT(a, each _{0}),
c = LT(a, each Text.ToList(Text.Remove(_{1}," "))),
Fx = (x)=> let
d = Table.Group(Table.FromColumns({x}),"Column1",{"G", each [Column1]},0)[G],
e = LT(d, each Table.FromRows({{List.Last(_)}&{List.Count(_)}})),
f = Table.ToRows(Table.MaxN(Table.Combine(e),"Column2",1)),
g = LT(f, each Text.Combine(LT(_, each Text.From(_))))
in g,
h = List.Combine(LT(c, each Fx(_))),
i = Table.FromRows(List.Zip({b,h}),{"Date","Length"}),
Sol = Table.ReplaceValue(i,"+","",Replacer.ReplaceText,{"Length"})
in
Sol
_x000D_
_x000D_
Power Query solution 4 for Pattern Length!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Length", (x)=>
Text.Combine(List.Transform(List.Last(List.Sort(List.Transform({"+", "-"}, (k)=>
let
a = x[Pattern],
b = List.Select(Text.Split(a, k), (z)=> z <> " "),
c = List.Transform(b, (y)=> Text.Length(Text.Select(y, Text.Remove("+-", k)))),
d = {Text.Remove("+-", k),List.Max(c)}
in d), each _{1})), Text.From)))[[Date],[Length]]
in
Sol
_x000D_
_x000D_
Power Query solution 5 for Pattern Length!, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WMjDU90rM0zcyMDJR0lHSVtBW0FVAkMgspVgdoHIjZOW6CgiojcTSRhGFaDRGtQdZMbJNEMUm6I5CV4bMg2gxxeUPZCfBNMQCAA==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Date = _t, Pattern = _t]
),
Result = Table.AddColumn(
Source,
"Length",
each [
a = Text.ToList(Text.Remove([Pattern], " ")),
b = Table.FromColumns({a}, {"Data"}),
c = Table.Group(b, "Data", {{"tmp", each Table.RowCount(_)}}, 0),
d = Table.Max(c, "tmp"),
e = d[Data] & Text.From(d[tmp])
][e]
)
in
Result
_x000D_
_x000D_
Power Query solution 6 for Pattern Length!, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"Length",
(x) =>
[
a = List.Select(Text.ToList(x[Pattern]), each Text.Contains("+-", _)),
b = Table.Group(
Table.FromColumns({a}),
"Column1",
{"Data", each List.Count([Column1])},
0,
(u, v) => Byte.From(v <> u)
),
c = Table.SelectRows(b, each [Data] = List.Max(b[Data])),
d = Text.Combine(List.Transform(Table.ToRows(c), each _{0} & Text.From(_{1})), ", ")
][d]
)[[Date], [Length]],
Result = Table.TransformColumnTypes(AddCol, {"Date", type date})
in
Result
_x000D_
_x000D_
Power Query solution 7 for Pattern Length!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Length",
each [
a = (x) =>
Text.From(
List.Max(
List.Transform(Text.Split(Text.Replace([Pattern], " ", ""), x), each Text.Length(_))
)
),
b = if a("-") > a("+") then "+" & a("-") else "-" & a("+")
][b]
)
in
B
_x000D_
_x000D_
Power Query solution 8 for Pattern Length!, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Logic = Table.AddColumn(
Source,
"Length",
each [
a = Text.ToList([Pattern]),
b = List.RemoveItems(a, {" "}),
c = Table.FromList(b, Splitter.SplitByNothing()),
d = Table.Group(c, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}}, 0),
e = Table.Sort(d, {"Count", Order.Descending}),
f = Table.FirstN(e, 1),
g = Table.AddColumn(f, "Length", each [Column1] & Text.From([Count]))[Length]{0}
][g]
)[[Date], [Length]]
in
Logic
_x000D_
_x000D_
Power Query solution 9 for Pattern Length!, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.AddColumn(Source, "Length", Fun)[[Date], [Length]],
Fun = each [
A = Text.Remove([Pattern], " "),
B = Text.Split(A, "+") & Text.Split(A, "-"),
C = List.Sort(B, {each Text.Length(_), - 1}){0},
D = Text.Start(C, 1) & Text.From(Text.Length(C))
][D]
in
Res
_x000D_
_x000D_
Power Query solution 10 for Pattern Length!, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
P = {"+","-"},
L = List.Transform,
C = Table.AddColumn(H, "Length", (x)=>
[
a = L(P, each Text.Split(x[Pattern],_)),
b = L(a, each Text.From(List.Max(L(_, each Text.Length(Text.Remove(_, " ")))))),
c = Text.Combine(List.Sort(List.Zip({List.Reverse(P), b}), {each _{1},1}){0})
] [c])[[Date],[Length]]
in C
_x000D_
_x000D_
Power Query solution 11 for Pattern Length!, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
TransFormCol = Table.TransformColumns(
Source,
{
"Pattern",
each [
a = Text.ToList(_),
b = List.RemoveItems(a, {" "}),
c = List.Positions(b),
d = List.Transform(
c,
(x) => if x = 0 then b{0} else if b{x} = b{x - 1} then b{x} else "|" & b{x}
),
e = List.Transform(d, (x) => if Text.Length(x) = 1 then {x} else Text.ToList(x)),
f = List.Combine(e),
g = Text.Combine(f),
h = Text.Split(g, "|"),
i = List.Transform(h, (x) => Text.Length(x)),
j = List.Max(i),
k = List.PositionOf(i, j, Occurrence.First),
l = List.Distinct(Text.ToList(h{k})){0} & Text.From(j)
][l]
}
)
in
TransFormCol
_x000D_
_x000D_
Power Query solution 12 for Pattern Length!, proposed by Tyler N.:
let
a = Table.AddColumn(
YourTable,
"Length",
each
let
c = Text.ToList(Text.Remove([Pattern], " ")),
f = List.Count(c),
g = (h, i, j, k, l) =>
if h = f - 2 then
l{List.PositionOf(j, i) - 1}{0} & Text.From(i)
else
let
m = (n) => c{n},
o = m(h) = m(h + 1),
p = Int8.From(o)
in
@g(
h + 1,
List.Max(j),
{j & {List.Count(k)}, j}{p},
{{c{h + 1}}, k & {c{h + 1}}}{p},
{l & {k}, l}{p}
),
q = g(0, 0, {0}, {c{0}}, {})
in
q
)
in
a[[Date], [Length]]
_x000D_
_x000D_
Power Query solution 13 for Pattern Length!, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom = Table.AddColumn(Source, "Length", each let
a = Table.FromList(List.Select(Text.ToList([Pattern]), each _<>" ")),
b = Table.Group(a, {"Column1"}, {{"Count", each Table.RowCount(_)}}, 0),
c = Table.SelectRows(b, each [Count]= List.Max(b[Count])),
d = Table.AddColumn(c, "Length", each [Column1]&Text.From([Count]))[Length]{0}
in d),
Final = Table.RemoveColumns(Custom,{"Pattern"})
in
Final
_x000D_
Solving the challenge of Pattern Length! with Excel
_x000D_
Excel solution 1 for Pattern Length!, proposed by 🇰🇷 Taeyong Shin:
=GROUPBY(
B2:B7,
VSTACK(
H2,
C3:C7
),
LAMBDA(
x,
LET(
r,
REGEXEXTRACT(
@x,
"[+ ]+|[- ]+",
1
),
l,
LEN(
r
)/2,
@SORTBY(
LEFT(
r
)&l,
-l
)
)
),
3,
0
)
_x000D_
_x000D_
Excel solution 2 for Pattern Length!, proposed by Oscar Mendez Roca Farell:
=MAP(
C3:C7,
LAMBDA(
a,
LET(
r,
REGEXEXTRACT(
SUBSTITUTE(
a,
" ",
""
),
"[+]+|[-]+",
1
),
e,
LEN(
r
),
@SORTBY(
LEFT(
r
)&e,
-e
)
)
)
)
_x000D_
_x000D_
Excel solution 3 for Pattern Length!, proposed by Julian Poeltl:
=MAP(
C3:C7,
LAMBDA(
P,
LET(
L,
LEN(
P
),
S,
SEQUENCE(
L
),
A,
LAMBDA(
A,
XMATCH(
L,
IF(
L-LEN(
SUBSTITUTE(
P,
REPT(
A,
S
),
""
)
),
S,
),
-1,
-1
)
),
Pl,
A(
"+ "
),
M,
A(
"- "
),
IF(
Pl>M,
"+"&Pl,
"-"&M
)
)
)
)
_x000D_
_x000D_
Excel solution 4 for Pattern Length!, proposed by Kris Jaganah:
=MAP(
C3:C7,
LAMBDA(
y,
LET(
a,
LAMBDA(
x,
MAX(
BYROW(
N(
TEXTSPLIT(
y,
" ",
x,
1,
,
""
)<>""
),
SUM
)
)
),
b,
a(
"-"
),
c,
a(
"+"
),
IF(
b>c,
"+"&b,
"-"&c
)
)
)
)
_x000D_
_x000D_
Excel solution 5 for Pattern Length!, proposed by Kris Jaganah:
=HSTACK(
B3:B7,
MAP(
C3:C7,
LAMBDA(
v,
LET(
a,
LAMBDA(
x,
MAX(
LEN(
TEXTSPLIT(
SUBSTITUTE(
v,
" ",
""
),
,
x,
1
)
)
)
),
b,
a(
"-"
),
c,
a(
"+"
),
IF(
b>c,
"+"&b,
"-"&c
)
)
)
)
)
_x000D_
_x000D_
Excel solution 6 for Pattern Length!, proposed by JvdV –:
=HSTACK(
B3:B7,
REDUCE(
"",
ROW(
1:99
),
LAMBDA(
x,
y,
IFNA(
REGEXEXTRACT(
C3:C7,
"([+-])( 1){"&y-1&"}",
2
)&y,
x
)
)
)
)
_x000D_
_x000D_
Excel solution 7 for Pattern Length!, proposed by Ivan William:
=MAP(
C3:C7,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
" "
),
b,
SCAN(
0,
a<>VSTACK(
0,
DROP(
a,
-1
)
),
SUM
),
c,
DROP(
FREQUENCY(
b,
b
),
-1
),
FILTER(
a,
c=MAX(
c
)
)&MAX(
c
)
)
)
)
_x000D_
_x000D_
Excel solution 8 for Pattern Length!, proposed by Sunny Baggu:
=MAP( C3:C7, LAMBDA(
p, LET(
_a,
MAX(
LEN(
SUBSTITUTE(
TEXTSPLIT(
p,
,
{" +",
"+ ",
"+",
" + "},
TRUE
),
" ",
""
)
)
),
_b,
MAX(
LEN(
SUBSTITUTE(
TEXTSPLIT(
p,
,
{" -",
"- ",
"-",
" - "},
TRUE
),
" ",
""
)
)
),
IF(
_a > _b,
"-" & _a,
"+" & _b
)
) ))
_x000D_
_x000D_
Excel solution 9 for Pattern Length!, proposed by Asheesh Pahwa:
=MAP(
C3:C7,
LAMBDA(
x,
LET(
p,
MAX(
LEN(
SUBSTITUTE(
TEXTSPLIT(
x,
"-"
),
" ",
""
)
)
),
m,
MAX(
LEN(
SUBSTITUTE(
TEXTSPLIT(
x,
"+"
),
" ",
""
)
)
),
IF(
p>m,
"+"&p,
"-"&m
)
)
)
)
_x000D_
_x000D_
Excel solution 10 for Pattern Length!, proposed by CA Mohit Saxena:
=VSTACK(B2:C2,
HSTACK(B3:B7,
MAP(C3:C7,
LAMBDA(a,
LET(p,
MAX(
LEN(
TEXTSPLIT(
a,
"-"
)
)
),
s,
MAX(LEN(TEXTSPLIT(a,
""+"))),IF(p›s,"+"&p,"-"&s))))))
_x000D_
_x000D_
Excel solution 11 for Pattern Length!, proposed by Fausto Bier:
=MAP(
C3:C7,
LAMBDA(
i,
LET(
k,
BYCOL(
DROP(
REDUCE(
0,
SEQUENCE(
LEN(
i
)
),
LAMBDA(
a,
v,
VSTACK(
a,
FIND(
REPT(
{"+ ",
"- "},
v
),
i
)
)
)
),
1
),
COUNT
),
b,
MAX(
k
),
@IF(
b=k,
"+"&b,
"-"&b
)
)
)
)
_x000D_
_x000D_
Excel solution 12 for Pattern Length!, proposed by ferhat CK:
=HSTACK(
B3:B7,
MAP(
C3:C7,
LAMBDA(
i,
LET(
q,
TOCOL(
REGEXEXTRACT(
SUBSTITUTE(
i,
" ",
""
),
".",
1
)
),
t,
TAKE,
a,
SCAN(
TRIM(
t(
q,
1
)
)&"-"&0,
TRIM(
q
),
LAMBDA(
x,
v,
IF(
v=LEFT(
x
),
x,
v&RIGHT(
x
)*1+1
)
)
),
b,
GROUPBY(
a,
a,
ROWS,
,
0
),
r,
FILTER(
b,
t(
b,
,
-1
)=MAX(
t(
b,
,
-1
)
)
),
CONCAT(
LEFT(
r
)
)
)
)
)
)
_x000D_
_x000D_
Excel solution 13 for Pattern Length!, proposed by ferhat CK:
=HSTACK(
B3:B7,
MAP(
C3:C7,
LAMBDA(
i,
LET(
a,
TOCOL(
VSTACK(
REGEXEXTRACT(
SUBSTITUTE(
i,
" ",
""
),
"[+]+",
1
),
REGEXEXTRACT(
SUBSTITUTE(
i,
" ",
""
),
"[-]+",
1
)
),
2
),
b,
LEN(
a
),
LEFT(
FILTER(
a,
MAX(
b
)=b
)
)&MAX(
b
)
)
)
)
)
_x000D_
_x000D_
Excel solution 14 for Pattern Length!, proposed by Hamidi Hamid:
=LET(
sb,
SUBSTITUTE,
x,
sb(
TRANSPOSE(
sb(
sb(
C3:C7,
"+",
1
),
"-",
2
)
),
" ", ),
y,
SUBSTITUTE(
TRANSPOSE(
sb(
SUBSTITUTE(
C3:C7,
"+",
2
),
"-",
1
)
),
" ", ),
sv,
TOCOL(
REPT(
2,
SEQUENCE(
,
20,
1
)
)
),
v,
IFERROR(
SEARCH(
sv,
x,
1
),
""
),
vv,
IFERROR(
-IF(
v<>"",
LEN(
sv
),
""
),
0
),
w,
IFERROR(
SEARCH(
sv,
y,
1
),
0
),
ww,
IFERROR(
-IF(
w<>0,
LEN(
sv
),
""
),
0
),
q,
-ABS(
vv
)+ABS(
ww
),
t,
IF(
q=0,
"",
q
),
HSTACK(
B3:B7,
TRANSPOSE(
BYCOL(
t,
LAMBDA(
a,
LOOKUP(
9^9,
a
)
)
)
)
)
)
_x000D_
_x000D_
Excel solution 15 for Pattern Length!, proposed by Hussein SATOUR:
=MAP(
C3:C7,
LAMBDA(
x,
LET(
a,
REGEXEXTRACT(
SUBSTITUTE(
x,
" ",
""
),
"[+]+|[-]+",
1
),
b,
LEN(
a
),
FILTER(
LEFT(
a
)&b,
b=MAX(
b
)
)
)
)
)
_x000D_
_x000D_
Excel solution 16 for Pattern Length!, proposed by Md. Zohurul Islam:
=LET( u,
B3:B7, v,
C3:C7, hdr,
{"Date",
"Length"}, w,
MAP(
v,
LAMBDA(
p,
LET(
a,
DROP(
TEXTSPLIT(
p,
,
" "
),
-1
),
b,
VSTACK(
0,
ABS(
DROP(
a,
1
)=DROP(
a,
-1
)