Forum Discussion
Reformating data from pivot tables
I have a data table that contains several pieces of information. It contains informations about the type of certificate received in each year for different education types. Unfortunately, I can only download the data in the long format as in the first added screenshot.
2 Replies
Removing the subtotals is simple, Select the pivot, click the Design tab and click the left-most button. Choose the top option.
I'm not sure what you mean by comparative time series.
What you probably want is to flatten everything out into a proper long format — one row per certificate, per year, per education type — with three columns like: EducationType, CertificateType, Year, Value. You can do this with Power Query really cleanly. If your raw data is structured like in the first screenshot, you can:
1 Load it into Power Query
2 Use “Unpivot Columns” on all the year columns
3 Then clean up the extra label rows (like those group headings: aso, bso, etc.) using a fill-down or by filtering Once it’s flattened, you’ll be able to build clean comparative time series without the subtotal junk or nesting issues.
Happy to walk you through the Power Query steps if needed.
