Unpivot Multiple Row Levels into Columns (attributes)

[Joorge_C]

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.

[Joorge_C · Accepted solution]

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]

@Joorge_C 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...

[Joorge_C]

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,

[Clara]

Oh I see! Thank you! I'll try that

[v-yuta-msft]

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

[Joorge_C]

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]

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