UNPIVOTHEADERS

[jaeiow]

UNPIVOTHEADERS function replicates the Power Query function "unpivot other columns", from the first column, where row labels expand out with their column values.
Functions in this formula: RBYROW, CBYCOL, repeatxtimes (below)

=LAMBDA(values,num_repeat,
DROP(REDUCE( "",
        SEQUENCE(ROWS(values)),
        LAMBDA(a, v, VSTACK(a, IF(SEQUENCE(INDEX(num_repeat, v, 1)), INDEX(values, v))))),1)
)

=LAMBDA(tableall,
LET(
    headers, DROP(CHOOSEROWS(tableall, 1), , 1),
    cols, COLUMNS(headers),
    rowlabels, DROP(CHOOSECOLS(tableall, 1), 1),
    r0s, ROWS(rowlabels),
    tabledata, DROP(tableall, 1, 1),
    SORTBY(
        WRAPCOLS(
            TOROW(
                IFNA(
                    VSTACK(
                        CBYCOL(headers, LAMBDA(x, repeatXtimes(x, r0s))),
                        SORT(rowlabels),
                        SORTBY(tabledata, rowlabels)
                    ),
                    SORT(EXPAND(rowlabels, r0s * 2, , ""))
                )
            ),
            cols * r0s
        ),
        RBYROW(rowlabels, LAMBDA(x, repeatXtimes(x, cols)))
    )
)
)

 

[jaeiow]

I think the math functions like quotient could be used to code this and avoid an issue. The issue I'm referencing is re-sort at the end. It doesn't work the way I did it. It is impossible, though it would be nice to re-sort against the original expanded row labels. So, for now, amended code removes the re-sort, and add the ability to apply a cosmetic shortening of the row labels to be one row for each.

=LAMBDA(tableall,[cosmetic],
    LET(
    headers, DROP(
        CHOOSEROWS(
        tableall,1)
        ,,1),
    cols, COLUMNS(headers),
    rowlabels, DROP(
        CHOOSECOLS(
        tableall,1)
        ,1),
    r0s, ROWS(rowlabels),
    WRAPCOLS(
    IFNA(
    TOROW(
        IFNA(
        VSTACK(
            CBYCOL(
                headers,LAMBDA(x,repeatXtimes(x,r0s))),
            SORT(
                rowlabels),
            SORTBY(
                DROP(
                    tableall,1,1),rowlabels)),
            SORT(
                EXPAND(
                rowlabels,IF(
                    OR(
                    ISOMITTED(
                        cosmetic),NOT(
                        cosmetic)),r0s *2,r0s),,0)))),""
        ),
        cols * r0s
    )
    )
)

 

[jaeiow]

And here is a/the solution that restores the original sort.

=LAMBDA(tableall,[cosmetic],
    LET(
        headers, DROP(CHOOSEROWS(tableall, 1), , 1),
        cols, COLUMNS(headers),
        rowlabels, DROP(CHOOSECOLS(tableall, 1), 1),
        r0s, ROWS(rowlabels),
        WRAPCOLS(
            TOROW(
                SORTBY(
                    IFNA(
                        VSTACK(
                            SORT(rowlabels),
                            CBYCOL(headers, LAMBDA(x, repeatXtimes(x, r0s))),
                            SORTBY(DROP(tableall, 1, 1), rowlabels)
                        ),
                        SORT(
                            EXPAND(
                                rowlabels,
                                IF(OR(ISOMITTED(cosmetic), NOT(cosmetic)), r0s * 2, r0s),
                                ,
                                ""
                            )
                        )
                    ),
                    TOCOL(
                        SORTBY(
                            RBYROW(SEQUENCE(r0s), LAMBDA(x, HSTACK(x, x + r0s, x + r0s * 2))),
                            rowlabels
                        ),
                        ,
                        TRUE
                    )
                )
            ),
            cols * r0s
        )
    )
)