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

SergeiBaklan

It gets worse than that!

If one simple focusses on the values (not the agent names or dates),

= TOCOL(CHOOSECOLS(rBravo,f))

the formula takes 0.7 ms.  On the other hand, if I use MAKEARRAY to calculate a thunk containing the agent, date and value triples for each value, then filter and unpivot the thunks, and finally use a second MAKEARRAY to extract the triple from each thunk

= LET(
      packedϑ, MAKEARRAY(200,16,
         LAMBDA(r,f,
            LAMBDA(CHOOSE({1,2,3}, 
               INDEX(nBravo,r),
               INDEX(dates,f), 
               INDEX(rBravo,r,f)
            )
         )
      )),
      mask,      LEFT(dates,1)<>"Q",
      filteredϑ, FILTER(packedϑ,mask),
      listϑ,     TOCOL(filteredϑ),
      MAKEARRAY(2400,3,
         LAMBDA(r₀,c,INDEX(INDEX(listϑ,r₀,1)(),c))
      )
   )

 takes 10,032 ms!     

DO NOT USE THIS!

I would rather like to hear an authoritative account of where the CPU cycles went!

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 23, 2022

    PeterBartholomew1 

    Charles could explain all details, but look, you call few thousand times values to memory, use slow FILTER and another few thousand times make calls on per-value basis. It shall be slower than call few times large blocks.

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      May 24, 2022

      SergeiBaklan 

      Sergei, perhaps I should send the file to Charles.  Patrick2788 's solution is the fastest.  I was deliberately trying different methods against the problem even where I suspected they would not be efficient.  I was not expecting to get differences in excess of 10,000 though!  Even if I were calculating 2400 terms of an array, selecting 1, and repeating the 2400 selecting the second etc. I would not expect the times to be that bad.  

       

      The attached file contains timing information for the methods posted here but with no study of size effects.

      • lori_m's avatar
        lori_m
        Iron Contributor
        May 24, 2022

        Hi Peter, I had a quick look at this after following your other link. Perhaps a simplified version might help highlight the source of the performance issues:

         

        =LET(thunks,IF(SEQUENCE(2400),LAMBDA({1,2,3})),
        MAKEARRAY(2400,3,LAMBDA(r,c,INDEX(INDEX(thunks,r,1)(),1,c))))

         

        Entering the above formula in any cell returns a 2400 x 3 array and takes 10+ seconds to calc. However this single row version calculates near instantaneously:

         

        =LET(thunks,IF(SEQUENCE(1),LAMBDA({1,2,3})),
        MAKEARRAY(2400,3,LAMBDA(r,c,INDEX(INDEX(thunks,1,1)(),1,c))))

         

        It seems that the thunk array might be being re-evaluated for each element of the MAKEARRAY result matrix - presumably this is due to 'lazy evaluation'. I've tended to steer clear of thunks so far but remain hopeful performance will improve over time.

Resources