7. Data Cleaning A–Z in Power Query
7.17 Unpivot and Pivot
Mitrano, many Excel reports store data "wide" (one column per month). Power BI works best with "long" (tall) data.
Before (wide)
| City | Jan | Feb | Mar |
|---|---|---|---|
| Pune | 1200 | 1350 | 1500 |
| Nashik | 800 | 860 | 910 |
After (long)
| City | Month | Target Orders |
|---|---|---|
| Pune | Jan | 1200 |
| Pune | Feb | 1350 |
| Pune | Mar | 1500 |
| Nashik | Jan | 800 |
| … | … | … |
(Made-up monthly order targets.)
Steps in Power BI
- Select the column(s) that should stay as they are (City).
- Transform › Unpivot Columns › Unpivot Other Columns.
- Rename Attribute →
Monthand Value →Target Orders. - Pivot (the reverse): select the column whose values should become headers (Month) › Transform › Pivot Column › Values Column:
Target Orders› Advanced options › Aggregate Value Function: Sum or Don't Aggregate › OK.
Unpivoted = Table.UnpivotOtherColumns(Source, {"City"}, "Month", "Target Orders"),
Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Month]), "Month", "Target Orders", List.Sum)
Why Unpivot Other Columns?
Unpivot Other Columns keeps the named columns and unpivots (स्तंभांच्या ओळी करणे) everything else. When April is added next month, it is unpivoted automatically. Unpivot Columns (selected ones only) hard-codes Jan/Feb/Mar and misses April.
Practice task
A target sheet has Area in rows and Blinkit/Amazon Now target orders in two columns. Unpivot it into Area, Platform, Target Orders. Then pivot it back to check your work.
Unpivot ekda samajla ki Excel madhle khup report "sudharta" yetat. Practice task nakki kara.
Ravindra Bagale's Tip
Mitrano, khup students unpivot by selecting the month columns and clicking Unpivot Columns, so next month's new column is ignored. Select the fixed columns (City, Store) and use Unpivot Other Columns instead, which automatically includes new month columns in future refreshes. He exam aani interview doghansathi important aahe.