Forum Discussion

Pussyk's avatar
Pussyk
Copper Contributor
Feb 14, 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 have only 2 columns, all years in 1 column (table 2). Simple transpose doesn't work. I also tried sumifs but didn't succeed. Please advise.

Formulas and Functions

5 Replies

  • 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.

         

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 14, 2021

    Pussyk 

    As Mr. Wyn Hopkins  said, Power Query is the most suitable tool for this.

    Here is some information and help for the first steps from Microsoft.

    Combine data from multiple data sources (Power Query)

    https://support.microsoft.com/en-us/office/combine-data-from-multiple-data-sources-power-query-70cfe661-5a2a-4d9d-a4fe-586cc7878c7d?ui=en-US&rs=en-US&ad=US

     

    * Knowing the Excel version and operating system would also be an advantage.

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

Resources