Excel BI Challenge Workout 200

Workouts Excel BI Challenge
7

LinkedIn Post by:
Hussein SATOUR

Thank you Excel BI

=LET(w,A2:A10,FILTER(w, MAP(w,LAMBDA(y,LET(a, MID(y,SEQUENCE(LEN(y)),1),b,MAP(UNIQUE(a),LAMBDA(x, SUM((a=x)*1))),SUM((b=AVERAGE(b))*1)=COUNTA(UNIQUE(a)))))))

8

LinkedIn Post by:
Zoran Milokanović

Listing isograms w/ #powerquery. #bitanbit #powerbi

let
Solution = [List.Select](http://list.select/)(Excel.CurrentWorkbook(){[Name="Input"]}[Content][Words], each let l = Text.ToList(_) in List.Distinct(l) = List.Modes(l))
in
Solution
9

LinkedIn Post by:
John Jairo V.

Hi to all!

:white_check_mark:

One option could be:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(c,CODE(MID(x,SEQUENCE(LEN(x)),1)),ROWS(UNIQUE(FREQUENCY(c,c)))=2))))

Blessings!

10

LinkedIn Post by:
Victor Yemitan

Here is my solution:

=LET(r, A2:A10,FILTER(r,MAP(r,LAMBDA(x,ROWS(UNIQUE(LEN(x)-LEN(SUBSTITUTE(x,MID(x,SEQUENCE(LEN(x)),1),""))))))=1))

11

LinkedIn Post by:
Pieter de B.

=LET(a,A2:A10,FILTER(a,MAP(a,LEN(a),LAMBDA(x,y,LET(b,MID(x,SEQUENCE(y),1),c,XMATCH(b,b),ROWS(UNIQUE(MMULT(N(TOROW(c)=c),SEQUENCE(y)^0)))=1)))))

12

LinkedIn Post by:
Kris Jaganah

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(y,LET(a,MID(y,SEQUENCE(LEN(y)),1),COUNT(UNIQUE(MAP(UNIQUE(a),LAMBDA(x,SUM(--(a=x)))))))))=1)

13

LinkedIn Post by:
Luan Rodrigues
 

let
 Fonte = Tabela1,
 res = Table.SelectRows(Fonte, each List.Count(
 List.Distinct(
 Table.Group(Table.FromColumns({Text.ToList([Words])}), {"Column1"},{{"Count", each Table.RowCount(_)}})[Count])) = 1)
in
 res
14

LinkedIn post by:
Bo Rydobon

M-Code 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Ans = Table.SelectRows(Source, each List.Count(List.Distinct(List.Transform(Text.ToList([Words]),(a)=> Text.Length(Text.Replace([Words],a,"")))))=1)
in
 Ans
15

LinkedIn Post by:
Rick Rothstein

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(s,LEN(SUBSTITUTE(x,UNIQUE(MID(x,SEQUENCE(,LEN(x)),1)),"")),AND(s=INDEX(s,,1))))))

16

Linkedin Post by:
Sunny Baggu
 

=FILTER(
 A2:A10,
 MAP(
 A2:A10,
 LAMBDA(a,
 LET(
 _tsplit, MID(a, SEQUENCE(LEN(a)), 1),
 _uniq, UNIQUE(_tsplit),
 _cnt, SCAN("", _uniq, LAMBDA(a, v, ROWS(FILTER(_tsplit, _tsplit = v)))),
 _cri, AND(DROP(_cnt, 1) = DROP(_cnt, -1)),
 _cri
 )
 )
 )
)
17

LinkedIn Post by:
Paolo Pozzoli

Nice one!
Please take a look at my approach in the attached image.

image
image740×338 80.6 KB

18

LinkedIn post by:
roberto mensa

=FILTER(A2:A10,MMULT(--(LEN(SUBSTITUTE(A2:A10,MID(A2:A10,SEQUENCE(,20),1),""))=LEN(SUBSTITUTE(A2:A10,MID(A2:A10,1,1),""))),SEQUENCE(20,,,0))=LEN(A2:A10))

19

LinkedIn Post by:
Guillermo Arroyo

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(b,CODE(MID(a,SEQUENCE(LEN(a)),1)),COUNT(UNIQUE(FREQUENCY(b,b)))=2))))

20

LinkedIn Post by:
Stevenson Yu
 

=FILTER(A2:A10, MAP(A2:A10, LAMBDA(X, LET(A,X,
B,MID(A,SEQUENCE(LEN(A)),1),
C,UNIQUE(B),
D,MOD(ROWS(B)/ROWS(C),1)=0,
D))))
21

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

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(v,SEQUENCE(50),r,MID(a,v,1),ROWS(UNIQUE(MMULT(--(r=TOROW(r)),
v^0)))=2))))
22

LinkedIn Post by:
Daniel G.

=LET(l,A2:A10,FILTER(l,MAP(l,LAMBDA(w,LET(m,MID(w,SEQUENCE(LEN(w)),1),ROWS(UNIQUE(MAP(m,LAMBDA(x,SUM(--(m=x)))))))))=1))

23

LinkedIn post by:
Brian Julius

My hashtag#powerquery

solution:

let
Source = Excel.CurrentWorkbook(){[Name=“Table1”]}[Content],
ToList = Table.ExpandListColumn( Table.AddColumn(Source, “Letters”, each Text.ToList( [Words] )), “Letters”),
Group1 = Table.Distinct( Table.RemoveColumns( Table.Group(ToList, {“Words”, “Letters”}, {{“Count”, each Table.RowCount(), Int64.Type}}), “Letters”)),
Group2 = Table.Group(Group1, {“Words”}, {{“Count”, each Table.RowCount(), Int64.Type}}),
FilterNClean = Table.RenameColumns( Table.RemoveColumns( Table.SelectRows(Group2, each ([Count] = 1)), “Count”), {“Words”, “Expected Answer”})
in
FilterNClean

24

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

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(v,SEQUENCE(50),r,MID(a,v,1),ROWS(UNIQUE(MMULT(--(r=TOROW(r)), v^0)))=2))))

25

LinkedIn Post by:
Bo Rydobon

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,ROWS(UNIQUE(LEN(SUBSTITUTE(a,MID(a,SEQUENCE(LEN(a)),1),))))=1)))

26

Power Query

let
    Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
    Return = Table.SelectRows (
        Source,
        each [
            S = Text.ToList ( [Words] ),
            C = List.Transform ( S, ( f ) => List.Count ( List.Select ( S, ( x ) => f = x ) ) ),
            R = List.Count ( List.Distinct ( C ) ) = 1
        ][R]
    )
in
    Return

Isogram.xlsx (23.0 KB)