If you learn one reshape in your career, learn this one. Unpivot turns a wide crosstab, where a variable's values have been spread across the column headers, into tidy long form, where every row is one observation and every column is one variable. It's the most valuable transformation in analytics, because virtually every downstream tool wants tidy data. PivotTables, charts, the Data Model, SUMIFS and FILTER all expect it. Meanwhile, virtually every export from a finance system or a manager's spreadsheet arrives as a crosstab. Unpivot is how you get from one to the other.
So what does "tidy" mean? A table is tidy when (1) each variable is a column, (2) each observation is a row and (3) each cell is one value. A crosstab breaks those rules. Picture a "product × month" grid where the months Jan, Feb, Mar, Apr are column headers and each cell is a sales figure. Month is a variable masquerading as four column names, and the single variable "sales" is smeared across four columns. A human can read that shape, but it's useless to a machine. You can't easily filter it to Q1, you can't sum by month, and you can't feed it to a PivotTable cleanly.
Advertisement
How do you unpivot columns in Power Query?
To unpivot it, start by selecting the column or columns you want to keep as identifiers, which here is the Product column. Then right-click that column ▸ Unpivot Other Columns, or use Transform ▸ Unpivot Columns ▸ Unpivot Other Columns, which does the same thing. Power Query collapses all the other columns into just two. An Attribute column holds the month name from the old header, and a Value column holds the cell figure. Rename Attribute → Month and Value → Sales by double-clicking the header, or with right-click ▸ Rename. A grid of P products across M month-columns becomes P × M rows, one row per product-month observation.
Why Unpivot Other Columns, and why not plain Unpivot Columns? Always prefer Unpivot Other Columns, selecting the identifiers to keep. Say a May column appears next month. Unpivot Other Columns sweeps it in automatically, because it unpivots everything that isn't Product. Unpivot Selected Columns would have named Jan…Apr explicitly and simply ignored May. Selecting the stable identifier is the choice that survives a refresh.
How do you reverse an unpivot?
Pivot is the inverse. Transform ▸ Pivot Column takes a long Attribute/Value pair and spreads it back into a crosstab. That's handy for a final presentation layer, but you'll almost always unpivot on the way in and let a PivotTable do any crosstabbing on the way out.