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

Patrick2788 

 

I believe this will work:

 

=LET(
  dat,$A$2:$Q$202,
  h,INDEX(dat,1,),
  filt,FILTER(dat,LEFT(h,1)<>"Q"),
  fr,ROWS(filt),
  fc,COLUMNS(filt),
  outr,(fr-1)*(fc-1)+1,
  out,MAKEARRAY(outr,3,
        LAMBDA(r,c,
          LET(
          colindex,MOD(r-2,fc-1)+2,
          rowindex,QUOTIENT(r-2,fc-1)+2,
            IF(
              r=1,INDEX({"Agent","Month","Value"},1,c),
              CHOOSE(c,
                INDEX(filt,rowindex,1),
                INDEX(filt,1,colindex),
                INDEX(filt,rowindex,colindex)
              )
            )
          )
        )
      )
,out
)

Resources