Array Transformation functions

AUNPIVOTSTACK

Reference

» Reference, Discussion, & Example Applications:

About

Unpivots the main headers of a table, and stacks this together with its sub headers.

Calls ASTACK.

Inputs:

  • tb : the table, with headers, whose main header needs be unpivoted, and then stacked
  • ht : text value to give to main header

Code

M.S. Excel 
AUNPIVOTSTACK = LAMBDA(tb, ht,
    LET(
        mh, CHOOSEROWS(tb, 1),
        mhf, FILTER(mh, mh <> ""),
        sh, CHOOSEROWS(tb, 2),
        shu, UNIQUE(sh, TRUE),
        k, ASTACK(DROP(tb, 2), COLUMNS(shu)),
        f, TAKE(k, , 1),
        i, QUOTIENT(SEQUENCE(ROWS(k)) - 1, ROWS(tb) - 2) + 1,
        g, HSTACK(INDEX(mhf, i), k),
        l, FILTER(g, f <> ""),
        tbh, HSTACK(ht, shu),
        VSTACK(tbh, l)
    )
);