5.17 Unpivot: Wide to Long (Concept)
Reports often come wide (one column per month). Analysis tools (PivotTables, Power BI) need long data: one row per city per month.
Before (wide)
| City | Aug | Sep | Oct |
|---|---|---|---|
| Pune | 4,12,500 | 5,86,200 | 4,95,300 |
| Nashik | 1,48,900 | 1,96,400 | 1,62,700 |
After (long)
| City | Month | Sales |
|---|---|---|
| Pune | Aug | 4,12,500 |
| Pune | Sep | 5,86,200 |
| Pune | Oct | 4,95,300 |
| Nashik | Aug | 1,48,900 |
| Nashik | Sep | 1,96,400 |
| Nashik | Oct | 1,62,700 |
Steps in Excel (Power Query – full detail in Module 10.6)
- Click inside the wide table › Data › Get & Transform Data › From Table/Range.
- In Power Query, select the City column › Transform › Unpivot Columns ▾ › Unpivot Other Columns.
- Rename Attribute →
Month, Value →Sales› Home › Close & Load.
Why "Unpivot Other Columns"? When November is added next month, it is unpivoted automatically.
Ravindra Bagale's Tip
Many students try to build a PivotTable on wide data and drag each month in as a separate field – and then a "total by month" chart can't be made at all. The rule: one column = one kind of data (Month is one column, Sales is one column). When you see wide data, unpivot it first, then pivot.
Ravindra Bagale's Tip – मराठी
बरेच students wide data वर PivotTable बनवायचा प्रयत्न करतात आणि प्रत्येक month वेगळं field म्हणून ओढतात – मग "total by month" chart बनतच नाही. नियम: एक column = एक प्रकारचा data (Month हा एक column, Sales हा एक column). Wide data दिसला की आधी unpivot करा, मग pivot.
Ravindra Bagale's Tip – हिंदी
बहुत से students wide data पर PivotTable बनाने की कोशिश करते हैं और हर month को अलग field के रूप में खींचते हैं – फिर "total by month" chart बनता ही नहीं. नियम: एक column = एक तरह का data (Month एक column, Sales एक column). Wide data दिखे तो पहले unpivot करो, फिर pivot.
Practice task
Unpivot a city × month table (6 cities × 4 months) with Power Query, load it, and build a PivotTable of sales by month.