How to Unpivot Columns in Power Query
Unpivoting turns wide data into tall data — the shape PivotTables and charts prefer. It's Power Query's most useful reshape, and it takes about three clicks once you know the pattern.
Wide vs. tall data
Wide format — one row per entity, with columns for each period or category:
Region Q1 Q2 Q3 Q4
North 12000 15000 18000 22000
South 9000 11000 13000 16000Tall format — one row per observation:
Region Quarter Amount
North Q1 12000
North Q2 15000
North Q3 18000
...Tall format is what most analytics tools want. Charts group by Quarter naturally. PivotTables sum, group, filter without contortions. Adding a Q5 (or Q1 of next year) doesn't require restructuring the sheet — it's just more rows.
Step 1: Open the query
Right-click your table → Edit Query. Or double-click the query in the Queries & Connections pane.
Step 2: Select the ID columns
Identify which columns describe each row (things that should stay as columns). In our example: Region.
Click the first ID column. Ctrl+click any additional ID columns.
Step 3: Unpivot other columns
Note the choice: Unpivot Other Columns. This unpivots everything except the ones you selected. Result: your ID columns stay put; every remaining column collapses into two new ones — Attribute (the column name) and Value.
Suppose next month a Q5 column appears. If you unpivoted specific columns (Q1-Q4), the new one is ignored. If you unpivoted other columns, the new one is included automatically. Always prefer the "Other Columns" variant for resilience.
Step 4: Rename the new columns
Double-click "Attribute" → rename to "Quarter". Double-click "Value" → rename to "Amount".
Step 5: Set the type
The Value column comes out as text. Click its type icon → Whole Number (or Decimal Number for currency).
Step 6: Close and Load
Home → Close & Load. The tall data appears in a new sheet, ready for a PivotTable or chart.
The M behind unpivot
Advanced Editor shows the generated M:
First argument: source table. Second: columns to keep as identifiers. Third: new column name for the labels. Fourth: new column name for the values.
Common patterns
Monthly columns to a monthly time series
Given Jan-Dec as columns, unpivot to two columns: Month, Amount. Now you can chart the year as a time series.
Survey questions to responses
Given Q1, Q2, Q3, … columns (each question a column) and one row per respondent, unpivot to three columns: Respondent, Question, Response. Now you can group by question or count response distributions.
Pivot back
If you need to reshape tall back to wide: Transform → Pivot Column. Select the column with the labels (Quarter), and pick the values column (Amount). Wide format returns.
Unpivot for hierarchical column headers
Some reports have two-level headers: Category above (Retail, Wholesale), Quarter below. Power Query imports these as flat column names like "Retail_Q1", "Retail_Q2". After unpivoting, split the Attribute column by delimiter to separate Category and Quarter.
Common pitfalls
Selecting the value columns and choosing "Unpivot Columns" works, but "Unpivot Other Columns" (selecting the identifiers) is more resilient to new columns being added.
If your wide data has empty cells (no sale in Q3), unpivoting produces rows with null Amounts. Decide whether to drop them (right-click Amount column → Remove Empty) or fill with 0 (Replace Values → null → 0).
Unpivoting collapses all value columns into one. If some were numeric and some text, the combined column becomes text. Restructure the source, or split back after unpivoting.
Reshaping without thinking about it
Whether to pivot or unpivot, which columns are identifiers, how to handle nulls — reshaping decisions add up. Excel Wizard looks at your data and target ("I want to chart this as a time series") and picks the right reshape automatically.
Install Excel Wizard →