Forum Discussion

Jennifer Lefief's avatar
Jennifer Lefief
Copper Contributor
Dec 13, 2016

help organize pivot table

I am trying to work with an existing MASSIVE set of data from a data table. The table is huge so I am trying to stick with what is there.

 

Simplified example of the table:

 

https://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=61295592-a80a-4b38-ab3b-0a1ea75d1b7d

 

My pivot is pulling in data like this:

https://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=b5d05fb7-1062-4e19-9144-6d7a79a6b5c0

 

But I WANT my data to look like this below.  Reworking the data set is not really an option at this point.  Any suggestion on how to make my pivot look like this:

https://fud.community.services.support.microsoft.com/Fud/FileDownloadHandler.ashx?fid=e5a14160-c7ab-486f-bf36-4c33099ea408

 

THANK YOU!

Jen

Formulas & Functions
Need Help

1 Reply

  • Dany Hoter's avatar
    Dany Hoter
    Former Employee
    Dec 14, 2016

    Hi Jennifer 

     

    You need to reshape the data so it will have the year in one column

    This is called unpivot and it is available in Power Query which is an free add-in to Excel for 2010 and 2013

    If you use 2016 it is included in the data ribbon.

    Unpivot is very simple if you use Power Query and very complex if you are not

    The result table will have many more rows and you say that the table is already pretty big

    If the end result will contain more than a million rows. You will need to put the result of the unpivot into the Excel data model (Only in 2013 or 2016)

    I can't go here over every step you need to go thru but there is a lot examples available on youtbe and other venues for unpivot in Power Query

     

    Dany Hoter (Excel) 

Resources