BYROW limitations: ranges vs. arrays

Hi, I've read a number of threads on the problems BYROW has, which I guess have mostly to do with not being able to handle arrays of arrays/nested arrays.

Some common workarounds I've seen employ REDUCE which I find a bit unwieldy due to slow calculation speed with large data sets and REDUCE coming along with its own drawbacks, such as often involving dropping the first returned result (not always clear to me why) and and formulas delivering different results/not working at all depending on the initial value of e.g. '=REDUCE(,...' vs. '=REDUCE(0,...' vs. 'REDUCE("",...' (also not clear to me why).

So I turned to MAP solutions which seems to be considered more robust in situations where BYROW fails.

In the attached file I wrote down a common problem of BYROW that doesn't seem to exist with MAP.
BYROW in that example works with a range reference, but not with a SEQUENCE-function containing the same as the referenced range nor with a TRANSPOSE-function of the referenced range.
MAP works in all scenarios described.

I found the following article (response by Sergei Baklan) quite helpful in navigating the situation, i.e. employing some =INDEX(i,1,1)-construct:
https://techcommunity.microsoft.com/...ow/m-p/3777828
It got the previously incorrectly working BYROW-formulas to return correct results in line with the results returned by MAP.

However, I still don't think I fully understand what's going on here, so happy if somone maybe has some more useful explanation or reference for me on the topic of what exactly is the issue with BYROW here, why MAP works (but BYROW not), see e.g. the 2 questions raised in the Excel file.
In some places this behaviour has been described as a bug, but it seems like not everybody agrees on this.
Fundamentally it seems to come down to an array not being the same as a range reference in all circumstances/formulas alike?

Similar to the following simplified problem:

=SEQUENCE({2})

returning 1 despite a formula evaluation shows the array,

{1;2}

which - upon pressing ENTER - spills correctly, whereas

=SEQUENCE(INDEX({2},1))

returns and spills correctly:

{1;2}

Compare this to e.g.:

=SUM(1,2,{2})

working without having to reference the array via INDEX.

Any help, explanation or guidance for further reading is highly appreciated.

Thanks

Maybe you need to consider BYCOL where the array is horizontal:

=TRANSPOSE(BYCOL(D2#,LAMBDA(a,TEXTJOIN(", ",,SEQUENCE(5,,a)))))

To add to Fluff13's comment. If you nest "a" in INDEX() it will convert array into single value and will work.
Ex: =BYROW(TRANSPOSE(D2#),LAMBDA(a,TEXTJOIN(", ",,SEQUENCE(5,,INDEX(a,1)))))

Thanks CK76.

btw - for the sake of good order, this weird little thing also works.

=BYROW(TRANSPOSE(D2#),LAMBDA(a,TEXTJOIN(", ",,SEQUENCE(5,,@a))))

O.o

That's new array syntax.

Sort of like [@TableColumn].

It works within that formula construct, as Transpose(D2#) removes direct cell reference context.

If you use it without, it's going to return #Value when you copy formula construct outside of row range in D2#.
EX:

=LET(a, $B$2#, @a)

On the other hand...

=LET(a, TRANSPOSE($D$2#),@a)

This will always return 1.

Ah, that's because BYROW/LAMBDA does same thing as TRANSPOSE.

So you can copy paste formula, as 'a' loses cell reference context in evaluation. It will evaluate within context of LAMBDA iteration by row.
Not relative to cell reference.