# Excel BI Challenge Workout 201

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

Find the next number which is having all digits distinct.
Example for 99, next number which is having all distinct digits is 102.

(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)

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

`=LET(f,LAMBDA(a,b,IF(b=--CONCAT(UNIQUE(MID(b,SEQUENCE(LEN(b)),1))),b,a(a,b+1))),MAP(A2:A8,LAMBDA(c,f(f,c+1))))`

Optimize without Brute force
for 1,100,000,000 => 1,203,456,789
that is 103,456,789 increment with Number+1 will take forever

Fast one
``````=MAP(A2:A8+1,LAMBDA(a,LET(s,SEQUENCE(,10,0),
r,--REDUCE(LEFT(a)+{0;1},SEQUENCE(LEN(a)-1),LAMBDA(c,w,
TAKE(TOCOL(IFS(ISERR(FIND(s,c))*(--c>=--LEFT(a,w)),c&s),3),10))),
XLOOKUP(a,r,r,,1))))
``````

This formula iterates considerably less than my previous solution.

`=LET(m,LAMBDA(n,--MID(n,SEQUENCE(LEN(n)),1)),l,SEQUENCE(10,,0),MAP(A2:A10+1,LAMBDA(a,LET(p,LAMBDA(q,r,t,s,i,IF(i>s,r,LET(u,MIN(REDUCE(10,l,LAMBDA(d,e,IF(OR(INDEX(m(t),i)>e,OR(m(r)=e)),d,VSTACK(d,e))))),IF(u=10,q(q,0,(r+1)*10^(s-i+1),LEN((r+1)*10^(s-i+1)),1),q(q,r*10+u,IF(@INDEX(m(t),i)=u,t,(r*10+u)*10^(s-i)),s,i+1))))),p(p,0,a,LEN(a),1)))))`

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
()=> [x = [Number]+1, y = 0, z = 1],
each [y] <> [z],
each [x = [x] + 1,
y = List.Count(Text.ToList(Text.From([x]))),
z = List.Count(List.Distinct(Text.ToList(Text.From([x]))))],
each [x])))
in
Sol
``````

Power Query

This code performs fewer iterations than my previous solution.

``````let
Origen = Excel.CurrentWorkbook(){[Name="Tabla2"]}[Content],
m = (y) => List.Transform(Text.ToList(Text.From(y)), each Number.FromText(_)),
l = {0..10},
a = List.Transform(Origen[Number], (x)=>
let
o = (r,t,s,i) =>
if i > s
then r
else
let
u = List.Min(List.Select(List.RemoveItems(l, m(r)), each _>= m(t){i-1}))
in
if u = 10
then @o(0, (r+1)*Number.Power(10,1+s-i), List.Count(m((r+1)*Number.Power(10,1+s-i))), 1)
else @o(r*10+u, if u=m(t){i-1} then t else (r*10+u)*Number.Power(10,s-i), s, i+1)
in
o(0, x+1, List.Count(m(x+1)), 1))
in
a
``````

``````=LET(
_list, A2:A8 + SEQUENCE(, 500),
_cond, MAP(
_list,
LAMBDA(a, LET(_m, LAMBDA(x, MID(x, SEQUENCE(LEN(x)), 1)), ROWS(_m(a)) = ROWS(UNIQUE(_m(a)))))
),
DROP(
REDUCE(
"",
SEQUENCE(ROWS(A2:A8)),
LAMBDA(a, v, VSTACK(a, XLOOKUP(TRUE, CHOOSEROWS(_cond, v), CHOOSEROWS(_list, v))))
),
1
)
)
``````

``````=LET(
_list, A2:A8 + SEQUENCE(, 500),
BYROW(
_list /
MAP(
_list,
LAMBDA(a, LET(_m, LAMBDA(x, MID(x, SEQUENCE(LEN(x)), 1)), ROWS(_m(a)) = ROWS(UNIQUE(_m(a)))))
),
LAMBDA(a, MIN(TOCOL(a, 2)))
)
)
``````

Power Query
``````let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform(Origen[Number], (x)=>
let
o = (y)=>
if y = Number.FromText(Text.Combine(List.Distinct(Text.ToList(Text.From(y)))))
then y
else @o(y+1)
in
o(x+1))
in
a
``````

M-Code
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformRows(Source, each List.Max(List.Generate( () => [Number]+1, each List.Count(List.Distinct(Text.ToList(Text.From(_)))) < Text.Length(Text.From(_)) , each _ + 1))+1 )
in
Ans
``````

[

### Zoran Milokanović

Next Number Distinct Digit w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each List.Last(List.Generate(() => [Number] + 1, each not List.IsDistinct(Text.ToList(Text.From(_))), each _ + 1, each _ + 1)))
in
Solution
``````

My solution (brute force) with

byrow

`=BYROW(MAP(A2:A8+SEQUENCE(,1000),LAMBDA(a,LET(n,LEN(a),u,COUNTA(UNIQUE(MID(a,SEQUENCE(n),1))),IF(n=u,a,"")))),LAMBDA(x,MIN(x)))`

`=MAP(A2:A8,LAMBDA(a,LET(r,SEQUENCE(300,,a+1),XLOOKUP(1,--MAP(r,LAMBDA(a,ROWS(UNIQUE(MID(a,ROW(1:9),1)))=LEN(a)+1)),r))))`

## LinkedIn Post by:John Jairo V.

Hi to all! One option could be:

`=MAP(A2:A8,LAMBDA(x,LET(n,x+ROW(1:999),@FILTER(n,MAP(n,LAMBDA(x,LEN(x)=ROWS(UNIQUE(MID(x,SEQUENCE(LEN(x)),1)))))))))`

Blessings!

`=MAP(A2:A8,LAMBDA(n,LET(s,SEQUENCE(999,,n+1),MIN(FILTER(s,MAP(s,LAMBDA(x,LEN(x)=ROWS(UNIQUE(MID(x,SEQUENCE(LEN(x)),1))))))))))`

## LinkedIn Post by: محمد حلمي

``````=MAP(A2:A8,LAMBDA(a,LET(r,SEQUENCE(300,,a+1),XLOOKUP(1,--MAP(r,LAMBDA(a,ROWS(UNIQUE(MID(a,SEQUENCE(
LEN(a)),1)))=LEN(a))),r))))
``````

`=MAP(A2:A8,LAMBDA(a,MIN(TOCOL(MAP(a+SEQUENCE(999),LAMBDA(s,s/(ROWS(UNIQUE(MID(s,SEQUENCE(LEN(s)),1)))=LEN(s)))),3))))`

`=LET(R,LAMBDA(R,n,LET(m,n+1,l,LEN(m),IF(ROWS(UNIQUE(MID(m,SEQUENCE(l),1)))=l,m,R(R,m)))),MAP(A2:A8,LAMBDA(a,R(R,a))))`

`=MAP(A2:A8,LAMBDA(n,MIN(MAP(SEQUENCE(999,,n+1),LAMBDA(x,IF(ISERROR(MODE(0+MID(x,SEQUENCE(LEN(x)),1))),x,""))))))`
`=MAP(A2:A8;LAMBDA(b;LET(a;SEQUENCE(1000);MIN(IF(MAP(b+a;LAMBDA(x;COUNTA(UNIQUE(MID(x;SEQUENCE(LEN(x));1)))))=MAP(b+a;LAMBDA(y;COUNTA(MID(y;SEQUENCE(LEN(y));1))));b+a;"")))))`