Excel BI Challenge Workout 183

[EnterpriseDNA]

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 183 —
(Solutions in any language are also welcome for Excel Challenges)

List Tetradic numbers from column A.
A tetradic number is a number that remains unchanged when reversed or flipped up-down. Since the only numbers that remain unchanged which turned up-side-down are 0, 1, and 8, a Tetradic number is precisely a palindromic number containing only 0, 1, and 8 as digits
Examples - 11, 101, 181, 18181, 1008001

Post answers in Comment.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

Download Practice File - https://lnkd.in/dwRUp65i

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

Excel BI LinkedIn Post

Excel BI on LinkedIn: #excel #excelchallenge #powerquerychallenge...

[EnterpriseDNA]

LinkedIn post by:
Amr Tawfik, CMA P1

Here is What Come to my mind

=FILTER(A2:A8,A2:A8=MAP(A2:A8,
LAMBDA(x,--TEXTJOIN("",,SORTBY(MID(x,SEQUENCE(1,LEN(x),1,1),1)
,SEQUENCE(,COUNTA(MID(x,SEQUENCE(1,LEN(x),-1,1),1)),1,

[EnterpriseDNA]

LinkedIn Post by:
Sunny Baggu

=LET(
    _input, A2:A8,
    _revinput, MAP(
        _input,
        LAMBDA(x, REDUCE("", SEQUENCE(LEN(x)), LAMBDA(a, v, MID(x, v, 1) & a)))
    ),
    _cond1, _input - _revinput = 0,
    _cond2, BYROW(_input, LAMBDA(a, SUM(LEN(TEXTSPLIT(a, {0; 1; 8}))) = 0)),
    FILTER(_input, MAP(_cond1, _cond2, LAMBDA(x, y, AND(x, y))))
)

[EnterpriseDNA]

LinkedIn post by:
Alejandro Simón

let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
Sel = [List.Select](http://list.select/)(List.Transform(Origen[Numbers], each Text.ToList( Text.From(_))), each _ = List.Reverse(_) and not List.ContainsAny(_, {"2".."7"}&{ "9"})),
Sol = List.Transform(Sel, each Number.From(Text.Combine(_, "")))
in
Sol

[EnterpriseDNA]

LinkedIn Post by:
Rafael González B.

Hi Everyone, Here’s My M Code:

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  ChangeToText = Table.TransformColumnTypes(Source,{{"Numbers", type text}}),
  Matching = Table.AddColumn(ChangeToText, "Match", 
                 each List.ContainsAll(
                   Text.ToList([Numbers]), {"0","1", "8"}
                   )
              ),
  Result = Table.SelectRows(Matching, each ([Match] = true))[[Numbers]]
in
  Result

[EnterpriseDNA]

LinkedIn Post by:
Sunny Baggu

=FILTER(
    A2:A8,
    MAP(
        A2:A8,
        LAMBDA(a,
            LET(
                _order, MID(a, SEQUENCE(LEN(a)), 1) + 0,
                _revorder, MID(a, SEQUENCE(LEN(a), , LEN(a), -1), 1),
                _cond1, AND(ISNUMBER(XLOOKUP(_order, {0; 1; 8}, {0; 1; 8}))),
                _cond2, AND(_order - _revorder = 0),
                AND(_cond1, _cond2)
            )
        )
    )
)

[EnterpriseDNA]

LinkedIn Post by:
Brian Julius

My #powerquery solution:

let
Source = Table.TransformColumnTypes( Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Numbers", Text.Type}),
Only018 = Table.AddColumn(Source, "Answer", each [
a = Text.ToList( [Numbers] ),
b = [List.Select](http://list.select/)(a, each not List.ContainsAny(a, {"2", "3", "4", "5", "6", "7", "9"})),
c = Text.Combine( b, "")
][c]),
Reverse = Table.AddColumn(Only018, "Custom", each Text.Reverse([Answer])),
Clean = Table.SelectColumns( Table.SelectRows( Reverse, each [Numbers] = [Custom]), "Answer")
in
Clean

[EnterpriseDNA]

LinkedIn post by
Guillermo Arroyo

Power Query

let
  Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
  a = Table.SelectRows(Origen, each 
      let 
        b = [Numbers] = Number.FromText(Text.Reverse(Text.From([Numbers]))),
        c = Text.Remove(Text.From([Numbers]), {"0", "1", "8"}) = ""
      in 
        b and c)
in
  a

[EnterpriseDNA]

LinkedIn post by:
Quadri Olayinka Atharu

=TOCOL(MAP(A2:A8,
LAMBDA(y,
LET(s,--MID(y,SEQUENCE(LEN(y)),1),
tnt,UNIQUE((s=1)+(s=8)+(s=0)),
reverse,--(CONCAT(INDEX(s,SORT(SEQUENCE(LEN(y)),,-1)))),
FILTER(y,(reverse=y)*(tnt=1))))),3)


tnt- Tetradic number test
s - number split into rows

[EnterpriseDNA]

LinkedIn Post by:
Zoran Milokanović

Tetradic numbers w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = [List.Select](http://list.select/)(Source[Numbers], each
let
l = Text.ToList(Text.From(_))
in
l = List.Reverse(l) and not List.ContainsAny(l, List.Difference({"0".."9"}, {"0", "1", "8"}))
)
in
Solution

[EnterpriseDNA]

LinkedIn post by:
Stevenson YuView Stevenson Yu’s profile

Extremely dirty, but…

=LET(Y, MAP(A2:A8, LAMBDA(X,
LET(A, X,
C, SUM(IFERROR(FIND({"2";"3";"4";"5";"6";"7";"9"},A),0)),
D, IF((A=CONCAT(MID(A, SEQUENCE(LEN(A),,LEN(A),-1),1)))*(C=0),A,""),D))), FILTER(Y,Y<>""))

[EnterpriseDNA]

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

=LET(n, A2:A8, FILTER(n, MAP(n, LAMBDA(x, LET(a, MID(x, SEQUENCE(LEN(x),,LEN(x), -1),1), b, FILTER(a, (a <> "1") * (a <> "8") * (a <> "0"), TRUE), IF(AND(b, COUNT(b)=1), IF(--CONCAT(a)=x, TRUE, FALSE)))))))

[EnterpriseDNA]

LinkedIn post by
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=FILTER(A2:A8, MAP(A2:A8, LAMBDA(r, LET(_s, SEQUENCE(LEN(r)),_n, SUM(IFNA(INDEX({0,1,8}, XMATCH(--MID(r,_s,1), {0,1,8})), )*10^(_s-1)), r=_n))))

[EnterpriseDNA]

LinkedIn Post by
محمد حلمي

=FILTER(A2:A8,MAP(A2:A8,LAMBDA(a,LET(
e,LEN(a),
r,MID(a,SEQUENCE(e),1),
v,ROWS(r),
(SUM(--(MID(a,e-SEQUENCE(e)+1,1)=r))=v)*
SUM(--(r+0={1,8,0}))=v))))

[EnterpriseDNA]

LinkedIn post by
John Jairo V.

Hi to all!

One option could be:

=FILTER(A2:A8,MAP(A2:A8,LAMBDA(x,ISERR(TEXTSPLIT(x,{0;1;8},,1))*(--CONCAT(MID(x,16-ROW(1:15),1))=x))))

Blessings!

[EnterpriseDNA]

LinkedIn post by:
Luan Rodrigues

let
 Fonte = Tabela1,
 res = Table.SelectRows(Fonte, each (Text.From([Numbers]) = Text.Reverse(Text.From([Numbers])) = true) and List.ContainsAll({"0","1","8"}, Text.ToList(Text.From([Numbers]))) = true )
in
 res

[EnterpriseDNA]

LinkedIn Post by:
Bo Rydobon

=LET(a,A2:A8,r,SEQUENCE(15),b,--MID(a&REPT(0,15),TOROW(r),1),FILTER(a,MMULT(b*ISNUMBER(XMATCH(b,{0,1,8}))/10,10^r)=a))

[EnterpriseDNA]

LinkedIn post by:
Alexis Olson

Table.SelectRows(Source, each [Numbers] = Number.From(Text.Reverse([Text.Select](http://text.select/)(Number.ToText([Numbers]), {"0","1","8"}))))

[EnterpriseDNA]

LinkedIn Post by:
Paolo PozzoliView Paolo Pozzoli’s profile

Not my best shot but it seems to work

[EnterpriseDNA]

LinkedIn Post by:
Kris Jaganah

=FILTER(A2:A8,MAP(A2:A8,LAMBDA(x,(--CONCAT(MID(x,SEQUENCE(LEN(x),,LEN(x),-1),1))=x)*(CONCAT(TEXTSPLIT(x,{"1","8","0"}))=""))))