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
Khup students wide data var PivotTable banvaycha prayatna kartat aani pratyek month la vegla field odhtat – mag "total by month" chart banatach nahi. Niyam: ek column = ek prakarcha data (Month ha ek column, Sales ha ek column). Wide disla ki aadhi unpivot kara, mag 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.