V E Meganathan

Task 1: Given m, where m = n³ + (n-1)³ + (n-2)³ + ... + 1³, determine the value of n if it exists; otherwise, return -1. Solution: Excel: =LET(l,LAMBDA(ME,m,start,n,IF(start=m,n-1,IF(start>m,-1,ME(ME,m,start+n^3,n+1)))),l(l,25502500,0,1)) Power Query: let  m = 3025,  fx = (m as number, start as number, n as number ) as number => if start = m then n - 1 else if start > m then -1 else @fx(m,start + Number.Power(n,3), n + 1),  Result = Table.FromValue(fx(m,0,1)) in  Result An alternative...

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

The sample data includes Supplier Name and Sales Amount information, but the sales amounts are contained in a single cell separated by commas. The goal is to broadcast the supplier's name according to the number of transactions. Method 1: =CHOOSE({1,2},B3,TEXTSPLIT(C3,,", "))  Method 2: =IF({1,0},B3,TEXTSPLIT(C3,,", "))  Method 3: =LET(a,TEXTSPLIT(C3,,", "),HSTACK(IFNA(B3&{""},a),a))  For larger datasets, this formula can be incorporated within the REDUCE Function to process each row...