Ravindra BagaleCourses & study guides

10. Power Query in Excel

10.6 Unpivot

By Last updated:

Steps in Excel

  1. Load the wide city × month table (5.17) into Power Query.
  2. Select the City column (the column(s) to keep) › Transform › Any Column › Unpivot Columns ▾ › Unpivot Other Columns.
  3. Rename Attribute → Month, Value → Sales.
  4. Convert Month text to a date if needed: Add Column › Custom Column = Date.FromText("01-" & [Month] & "-2026", [Culture="en-IN"]) › type Date.
  5. Close & Load.

The reverse, Pivot Column (Transform › Any Column › Pivot Column), turns long data back into wide – rarely needed for analysis.

Worked example. A 6-city × 12-month target sheet from the finance team becomes 72 rows (City, Month, Target) that can be merged with actual sales by City and Month for a target-vs-actual PivotTable.

Ravindra Bagale's Tip

Khup students month columns select karun "Unpivot Columns" kartat – mag navin month (Dec) aala ki to unpivot hot nahi. City select karun Unpivot Other Columns vapra – mag navin columns automatic yetat. Unpivot nantar Month cha type (text/date) nakki set kara.

Practice task

Unpivot a monthly targets sheet, convert Month to a real date, merge it with actual monthly sales and load a target-vs-actual table.