Forum Discussion

Pussyk's avatar
Pussyk
Copper Contributor
Feb 13, 2021

Transposing a table

Hello,  I have a panel data table. The value of an item is recorded each year. The column has values corresponding to the year ( table 1). So, there are 11 years and 11 different columns. I want to ...
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Feb 14, 2021

Pussyk 

Unpivoting tables is core functionality of Power Query. 

It is also possible to effect such a transformation using formulas but that involves detailed messing about with row and column indices.  It is most elegantly performed in the latest 365 version of Excel.

 

= UNPIVOT(array, item, year)

which is a named version of

= LAMBDA(arr,Itm,Yr,
    LET(
      m, ROWS(arr),
      n, COLUMNS(arr),
      k, SEQUENCE(m*n),
      YearLst, INDEX(Yr, 1+MOD(k-1,n)),
      itemLst, INDEX(Itm, 1+QUOTIENT(k-1,n)),
    CHOOSE({1,2,3}, itemLst, YearLst, ELEMENT(itemLst, YearLst) ) )
  )(array, item, year)

The key elements of the calculation are the index 'k' numbering the output array and, from that, the MOD and QUOTIENT formulae that identify the row and column of element required from the array.

 

That should probably convince you to use PQ but it might be worth knowing other possibilities exist. 

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 14, 2021

    SergeiBaklan 

    Just a glorified INDEX but specific to a particular array

    = LAMBDA(itm,yr,
    LET(
    r0, XMATCH(itm, item),
    c0, XMATCH(yr, year),
    INDEX(array, r0, c0) )
    )

    The intention is that the Lambda function should be the Name the user knows the array by and the reference within the formula is to the range that the array occupies.   That gives a more standard function notation than the INDEX formula.

     

Resources