Forum Discussion

Patrick2788's avatar
Patrick2788
Silver Contributor
May 22, 2022

Unpivot Monthly Data with a Formula

Note: I know how to unpivot data using PowerQuery and vba.  What I'm interested in with this exercise is using a formula to unpivot monthly data.  I'm interested in using some of the newest functions...
Formulas and Functions
office 365
Patrick2788's avatar
Patrick2788
Silver Contributor
Aug 19, 2023

Patrick2788 

With this post being bumped, I thought it'd be a good idea to revisit and refine.

 

This effort is me trying to avoid the standard REDUCE/VSTACK method.

 

'UnPivot
=LAMBDA(range,LET(
    header, {"Name", "Month", "Val"},
    top, TAKE(range, 1),
    filtered, FILTER(range, LEFT(top) <> "Q"),
    c, SEQUENCE(COLUMNS(filtered) - 1, , 1, 0),
    agent, TOCOL(DROP(CHOOSECOLS(filtered, c), 1)),
    r, SEQUENCE(ROWS(agent) / COUNT(c), , 1, 0),
    months, TOCOL(DROP(CHOOSEROWS(filtered, r), , 1)),
    values, TOCOL(DROP(filtered, 1, 1)),
    VSTACK(header, HSTACK(agent, months, values))
))

 

Resources