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
mtarler's avatar
mtarler
Silver Contributor
May 22, 2022

Patrick2788  here is another, but again I wouldn't call it 'creative' as much as brute force...

=LET(rc,ROWS(nBravo),
     cc,COLUMNS(dates),
ungroup,MAKEARRAY(rc*cc,3,
             LAMBDA(r,c,CHOOSE(c,
                 INDEX(nBravo,QUOTIENT(r-1,cc)+1),
                 INDEX(dates,MOD(r-1,cc)+1),
                 INDEX(rBravo,QUOTIENT(r-1,cc)+1,MOD(r-1,cc)+1)
             ))),
    FILTER(ungroup,ISERROR(SEARCH("Total",INDEX(ungroup,,2)))))

Resources