# Excel BI Challenge Workout 200

— 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.

(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

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

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

## 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}}),
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
``````

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

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
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
``````

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

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

## LinkedIn Post by:John Jairo V.

Hi to all! 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!

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

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

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

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

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

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

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

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

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

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