Forum Discussion
Unpivot matrix
I am trying to make a current matrix of data. The matrix is currently set up such that the combination of % to quota (rows) and volume ranges (columns), results in a payout amount. I'm trying to find...
egspen2 Perhaps PowerQuery? See attached.
Thanks - for my own educational purposes, could you layout the steps that you went through to get to this result?
egspen2 If you are unfamiliar with PowerQuery, the link below would be a good place to start learning. In particular, chapter 13 deals with unpivoting data.
egspen2 This is almost identical to another recent post:
https://techcommunity.microsoft.com/t5/excel/unpivot-monthly-data-with-a-formula/td-p/3411637
you can get a few different options/techniques from that thread.
Here is my cell formula based answer from there modified slightly to your matrix (after I defined it as a table called "Data"):
=LET(rc,ROWS(Data),cc,COLUMNS(Data)-1,ungroup,MAKEARRAY(rc*cc,3,LAMBDA(r,c,CHOOSE(c,INDEX(Data,QUOTIENT(r-1,cc)+1,1),INDEX(Data[#Headers],MOD(r-1,cc)+2),INDEX(Data,QUOTIENT(r-1,cc)+1,MOD(r-1,cc)+2)))),ungroup)
