Excel BI Challenge Workout 201

Workouts Excel BI Challenge
2

LinkedIn Post by:
Guillermo Arroyo

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

3

LinkedIn Post by:
Bo Rydobon

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

image
image733×348 68.5 KB

4

LinkedIn post by:
Guillermo Arroyo

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

5

LinkedIn Post by:
Alejandro Simón

Adjunto mi query... 
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Sol = Table.AddColumn(Source, "Answer", each List.Last(List.Generate(
  ()=> [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
6

LinkedIn Post:
Guillermo Arroyo

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

image
image746×322 46.8 KB

7

LinkedIn post by:
Sunny Baggu
 

=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
    )
)
8

LinkedIn Post by:
Sunny Baggu
 

=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)))
    )
)
9

LinkedIn Post by:
Guillermo Arroyo

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

image
image735×241 26.3 KB

10

LinkedIn Post by:
Bo Rydobon

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
11

LinkedIn Post by:
[

Zoran Milokanović

](https://www.linkedin.com/in/ACoAAAEwhwwB2CI1Gk7IC6kZSO36Ug1bUvk9DGs)

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
12

LinkedIn post by:
LEONARD OCHEA

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

13

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

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

14

LinkedIn Post by:
John Jairo V.

Hi to all!

:white_check_mark:

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!

15

LinkedIn Post by:
Daniel G.

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

16

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

LinkedIn post by:
Bo Rydobon

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

18

LinkedIn Post by:
Bo Rydobon

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

19

LinkedIn Post by:
[

Rick Rothstein

](https://www.linkedin.com/in/ACoAADOOYgsBnBUfxpKNbbAMr3rtsabRuCQlPXo)

=MAP(A2:A8,LAMBDA(n,MIN(MAP(SEQUENCE(999,,n+1),LAMBDA(x,IF(ISERROR(MODE(0+MID(x,SEQUENCE(LEN(x)),1))),x,""))))))

20

LinkedIn Post by:
Tolga Demirci

=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;"")))))

21

Power Query

let
	MyFun  = ( Number ) =>
		let
			Add   = Number + 1,
			Split = Text.ToList ( Text.From ( Add ) ),
			Check = List.IsDistinct ( Split ),
			Final = if Check then Add else @MyFun ( Add )
		in
			Final,
	Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
	Return = Table.AddColumn ( Source, "Answer", each MyFun ( [Number] ) )
in
	Return

Next Number Distinct Digit.xlsx (24.0 KB)