10.6 Unpivot
Steps in Excel
- Load the wide city × month table (5.17) into Power Query.
- Select the City column (the column(s) to keep) › Transform › Any Column › Unpivot Columns ▾ › Unpivot Other Columns.
- Rename Attribute →
Month, Value →Sales. - Convert Month text to a date if needed: Add Column › Custom Column
= Date.FromText("01-" & [Month] & "-2026", [Culture="en-IN"])› type Date. - 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
Many students select the month columns and use "Unpivot Columns" – then when a new month (Dec) arrives, it doesn't get unpivoted. Select City and use Unpivot Other Columns – then new columns come in automatically. After unpivoting, be sure to set the type of Month (text/date).
Ravindra Bagale's Tip – मराठी
बरेच students month columns select करून "Unpivot Columns" करतात – मग नवीन month (Dec) आला की तो unpivot होत नाही. City select करून Unpivot Other Columns वापरा – मग नवीन columns आपोआप येतात. Unpivot नंतर Month चा type (text/date) नक्की set करा.
Ravindra Bagale's Tip – हिंदी
बहुत से students month columns select करके "Unpivot Columns" करते हैं – फिर नया month (Dec) आता है तो वह unpivot नहीं होता. City select करके Unpivot Other Columns इस्तेमाल करो – फिर नए columns अपने-आप आ जाते हैं. Unpivot के बाद Month का type (text/date) ज़रूर set करो.
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.