Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Unpivot Multiple Row Levels into Columns (attributes)

Hello All,

Does anyone know the best way to Unpivot the multiple Rows into Columns?

Example

Move the Type 1, Type 2,FY, Month into Columns, while keeping the 3 Column values into a Single Column Value.

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hello All, I was able to acomplish this via a concatenation of the attributes into a single columns, then transposing the entire table and unpivoting all the columns into rows.

     

    Thanks all for your help and inputs!

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hello All, I was able to acomplish this via a concatenation of the attributes into a single columns, then transposing the entire table and unpivoting all the columns into rows.

     

    Thanks all for your help and inputs!

    • Clara's avatar
      Clara
      Advocate II
      7 years ago

      Anonymous Sorry, would you mind explaining how you did it? I'm having the same problem and not having any luck doing it the way you said...

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Clara,

        Try to concatenate all your main categorization attributes (because once you transpose and you will loose columns), then move this concatenated New Column to the begginign of the table (1st Column).

         

        Then Transpose the entire table and promote to headers (you should see the concatenated column at header level)

         

        Now select the values column and Pivot into a single column.

         

        Lastly just delimit your concatenated column to have all your attributes back broken in columns,

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi Joorge_C,

     

    What does "into a Single Column Value" mean? Could you please give a demo to clarify more details about it?

     

    Regards,

    Jimmy Tao

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hello Jimmy, Thanks for your help.

       

      So essentialy have a single Value Column with different rows for Months.

       

      Currently the Months and other Fields are as Columns, so Im trying to move them to Rows and keeping a Single colum row for the Sales Numeric Values we see now accross 3 Columns.

       

      Thanks

      Jorge

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        8 years ago

        Hi Joorge_C,

         

        As a general solution, you can duplicate the table, in the original table, you can remove rows under the first 4 rows, then unpivot these columns. In the second table, you an remove first 4 rows, then do "Unpivot Operation". Then merge two tables together.

         

        Regards,

        Jimmy Tao