Recursion in Excel and Power Query

Task 1:

Extract the string before any alphabet is repeated. Hence, in case of "Apple", the answer is "Ap". In case of "Debt", no alphabet is repeated. Hence answer is "Debt".

Solution:

Excel:

=LET(l,LAMBDA(ME,s,f,IF(ISNUMBER(SEARCH(LEFT(s),f)),f,ME(ME,MID(s,2,99),f&LEFT(s)))),MAP(A2:A10,LAMBDA(x,l(l,x,""))))

Power Query:

let

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

 fx = (s as text, optional f as text) as text =>

 [

 A = if f = null then "" else f,

 B = if Text.Contains(A, Text.Start(s,1), Comparer.OrdinalIgnoreCase ) then A else @fx( Text.Middle(s,1), A & Text.Start(s,1))

 ][B],

 Result = Table.AddColumn(Source,"Result", (f) => fx(f[String]))

in

 Result

Task 2:

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.

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