Need Help: Reshape Cross table in PowerPivot (unpivot)

[greenlion24]

Hello all, I would appreciate any help from you guys who are guru's or anyone else who may have the answer. I am just beginning to learn PowerPivot. I'm hoping that PowerPivot will solve many of the issues I deal with Excel that make me work extra just to do work-arounds.

Here's my issue in my workplace.. Data reports I receive, monthly, that I need to analyze come in basically a cross table format and I think before I can unleash the PowerPivot monster I need to fix this data so that it is more "flat" and can perform many pivot tables. I think the correct term would be to "unpivot" the data.

Can anyone tell me how to do this in PowerPivot or perhaps Power Query may be the answer even though I am not familiar with it.

Here is a brief description of the data file I receive:

First column show's list of Stat Line Items (variables - there are about 200 of them); Next column fields across are headed by Product Names (there are 150 products).. each containing data for the above product listed that pertains to the stat variable showing in the first column.

ex.

STAT PROD1 PROD2 PROD3 ....

Volume (value) (value) (value)
Transaction (value) (value) (value)
accounts (value) (value) (value)



Hopefully someone can guide me in the right direction so that I can move forward and start to make relationships with other tables that come in this same format. Thanks in advance to all your posts /help.

 

[RoryA]

You can certainly do it with PowerQuery - see for example Bob's post here (and its links): Unpivot Nested Headings With Power Query | Bob's BI Ramblings

You can also do it with a regular pivot table - see this post for example: Is it possible to "unpivot" or "reverse pivot" in Excel? - Super User

 

[greenlion24]

You can certainly do it with PowerQuery - see for example Bob's post here (and its links): Unpivot Nested Headings With Power Query | Bob's BI Ramblings

You can also do it with a regular pivot table - see this post for example: Is it possible to "unpivot" or "reverse pivot" in Excel? - Super User

Thank you for your help!. I'll check the links and try those methods.

PS - this is my first time on the forum and it's great to see that answers come in. Your help is really appreciated!

 

[RoryA]

Glad to help - and welcome!