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