Recursion in Excel and Power Query - 2

Task:

An anagram is a word which is made by arranging the letters of another word. 

For ex. dues, sued, used are anagrams of each other.

Find the list of words which are anagrams of each other. List the group of words in different rows.

Anagram Words.xlsx

Download XLSX • 20KB

Solution:

Excel:

=LET(l,LAMBDA(ME,x,LET(p,LAMBDA(q,CONCAT(SORT(MID(q,SEQUENCE(LEN(q)),1)))),a,FILTER(x,p(@TAKE(x,1))=MAP(x,LAMBDA(y,p(y))),""),IF(ROWS(x)=1,"",IF(ROWS(a)=1,ME(ME,DROP(x,1)),VSTACK(TOROW(a),ME(ME,FILTER(x,ISNA(XMATCH(x,a)),""))))))),IFNA(DROP(l(l,A2:A20),-1),""))

Power Query:

let

 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

 fx = (L as list) => [v = (x) => Text.Combine(List.Sort(Text.ToList(x))), l = List.Buffer(L), A = List.Select(l, (f) => v(f) = v(l{0})), B = if List.Count(A) = 1 then @fx(List.Skip(l)) else {A} & @fx(List.Difference(l,A)), C = if L = {} then {} else B][C],

 Result = Table.FromRows(Table.ToColumns(Table.FromColumns(fx(Source[Words]))))

in

 Result