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
Friends, many 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. This matters for both exams and interviews.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students month columns select करून Unpivot Columns वर click करतात, त्यामुळे पुढच्या महिन्याचा नवीन column दुर्लक्षित राहतो. त्याऐवजी fixed columns (City, Store) select करून Unpivot Other Columns वापरा, जो पुढच्या refreshes मध्ये नवीन month columns आपोआप घेतो. हे exam आणि interview दोन्हीसाठी important आहे.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students month columns select करके Unpivot Columns पर click करते हैं, तो अगले महीने का नया column छूट जाता है. इसकी जगह fixed columns (City, Store) select करके Unpivot Other Columns इस्तेमाल करो, जो आगे के refreshes में नए month columns अपने-आप ले लेता है. यह exam और interview दोनों के लिए ज़रूरी है.