Question about Lambda

From my understanding, within a LET formula, the letters x and q are like variables, then only become what you define them as. for example:

Formula:

x,UNIQUE(Table1[Name])

Will mean that the value of x becomes the below:
A
B
C
D

So x is an array of values and not just one.

With that in mind, when you use the BYROW function, it requires a LAMBDA in order to loop through the values of x. For example:

Formula:

BYROW(x,LAMBDA(q

In the above, the q becomes A then B then C then D as the LAMBDA loops through the values of x.

So the LAMBDA part is doing a TEXTJOIN using each value within x by looking up the values in your table, so it finds:

A - China, SG
B - SG, Msia
Etc..

The formula then stacks horizontally, the result of x and then the results for the TEXTJOIN's.

Sorry if this is not clear, this is the way I learned how the LAMBDA works, I actually come from a VBA coding background.

As for the formula to convert the values back, the below could be an option, although there may be many variations that do the same:

Formula:

=LET(a,Table3[DDD],b,Table3[EEE],x,TEXTJOIN("",TRUE,REPT(a&", ",LEN(b)-LEN(SUBSTITUTE(b,",",""))+1)),HSTACK(TEXTSPLIT(LEFT(x,LEN(x)-2),,", ",TRUE),TRIM(TEXTSPLIT(TEXTJOIN(",",TRUE,b),,","))))

@CheeseSandwich

Hi, thanks for the detailed explanation. Does that mean the q as in "Lambda(q" can only refer to a single cell?

Originally Posted by FT00

HDoes that mean the q as in "Lambda(q" can only refer to a single cell?

No.
In BYROW(x,LAMBDA(q,... q ferfers each time to a single row: The first time q refers to the first row of x, the second time q refers to the second row of x etcetera,
where in your example x is equal to UNIQUE(Table1[Name])

In MAP(x,LAMBDA(q,..., q can only refer ro a single cell.

Here is another solution to convert the values back:

Formula:

=DROP(TEXTSPLIT(CONCAT(MAP(Table3[DDD],Table3[EEE],LAMBDA(d,e,d&","&SUBSTITUTE(e,", ",";"&d&",")&";"))),",",";"),-1)

q refers to a single instance of x

No such thing - until you know, you don't know. I progress in fits and starts with LAMBDA, with a lightbulb moment every now and again. Lots of tiral and (frustrating) error will get you there eventually.