Excel BI Challenge Workout 200

Workouts Excel BI Challenge
1

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

List all isograms from column A - An isogram is that word where letters occur same number of times
Ex. 1. person - All letters appear only once.
2. geggee both g and e appear 3 times.

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/d4esxUKX

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

image
image800×504 109 KB

LinkedIn Post:

2

LinkedIn Post by:
LEONARD OCHEA

Solution1 :

with FILTER

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(e,MID(a,SEQUENCE(LEN(a)),1),u,UNIQUE(e),MOD(ROWS(e),ROWS(u))=0))))

Solution2 :

WithTOCOL

=TOCOL(MAP(A2:A10,LAMBDA(a,LET(e,MID(a,SEQUENCE(LEN(a)),1),u,UNIQUE(e),IF(MOD(ROWS(e),ROWS(u))=0,a,NA())))),2)

I have corrected the formulation for the case presented “deedee”

Edited Solution

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

3

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=LET(_d, A2:A10, FILTER(_d, MAP(_d, LAMBDA(a, LET(_s, SEQUENCE(LEN(a)),_w, MID(a,_s, 1), MOD(AVERAGE(MMULT(--(_w=TOROW(_w)),_s^0)), 1))))=0))

4

LinkedIn Post y:
Rafael González B.

Hi everyone, here's my M Code: 
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ChangeType = Table.TransformColumnTypes(Source,{{"Words", type text}}),
WordTransf = Table.AddColumn(ChangeType, "Anwer", each
let
a = Text.ToList([Words]),
b = Table.FromList(a, Splitter.SplitByNothing(), {"Letters"}, null, ExtraValues.Error),
c = [Table.Group](http://table.group/)(b, {"Letters"}, {{"Count", each Table.RowCount(_), Int64.Type}})[[Count]],
d = Table.Distinct(c)
in
d),
CountRows = Table.AddColumn(WordTransf, "AnwerDef", each Table.RowCount([Anwer])),
Result = Table.SelectRows(CountRows, each ([AnwerDef] = 1))[[Words]]
in
Result
5

LinkedIn Post by:
Guillermo Arroyo

Power Query 
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform(Origen[Words], (x)=> List.Count([List.Select](http://list.select/)(List.Distinct(List.Transform({"a".."z"}, each List.Count(Text.Split(x, _))-1)), each _>0))=1),
b = [List.Select](http://list.select/)(Origen[Words], each a{List.PositionOf(Origen[Words], _)})
in
b
6

LinkedIn Post by:
Alejandro Simón

Adjunto mi query... 
let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Sol = Table.SelectRows(Table.AddColumn(Source, "Custom", each 
    let
    a = Table.FromColumns({Text.ToList([Words])}),
    b = List.Distinct(Table.Group(a, {"Column1"}, {"Count", each List.Count([Column1])})[Count])
    in b), each List.Count([Custom])=1)[[Words]]
in
  Sol
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))))