10.6 Unpivot
Unpivot month columns in Power Query with example — in 30 seconds
What: Unpivot turns many month columns into two columns: Month and Value.
Why: PivotTables, charts and SUMIFS work best with one Month column. Wide tables need a new formula for every new month.
Tiny example: 2 cities × 3 month columns become 6 rows with a Month column.
Unpivot month columns in Power Query with example — 30 सेकंदात
काय: Unpivot अनेक month columns चे दोन columns करतो: Month आणि Value.
का: PivotTable, chart आणि SUMIFS एका Month column सोबत सगळ्यात छान चालतात. Wide table ला प्रत्येक नव्या महिन्यासाठी नवा formula लागतो.
छोटं उदाहरण: 2 शहरं × 3 month columns चे Month column सह 6 rows होतात.
Unpivot month columns in Power Query with example — 30 सेकंड में
क्या: Unpivot कई month columns को दो columns में बदलता है: Month और Value.
क्यों: PivotTable, chart और SUMIFS एक Month column के साथ सबसे अच्छे चलते हैं. Wide table में हर नए महीने के लिए नया formula लगता है.
छोटा उदाहरण: 2 शहर × 3 month columns, Month column के साथ 6 rows बन जाते हैं.
First look at the data (before → after):
Expected result — After Unpivot (long)
| City | Month | Orders |
|---|---|---|
| Pune | Sep | 410 |
| Pune | Oct | 456 |
| Pune | Nov | 523 |
| Nashik | Sep | 284 |
| Nashik | Oct | 301 |
| Nashik | Nov | 338 |
About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on unpivot.
Before table and formula as text
Before — Orders typed by month (wide)
| City | Sep | Oct | Nov |
|---|---|---|---|
| Pune | 410 | 456 | 523 |
| Nashik | 284 | 301 | 338 |
About this data: Demo table only — not real customer or company data. Built to practise unpivot.
Formula / click path
Select City › Transform › Unpivot Other Columns
Rename Attribute → Month, Value → Orders
Do it step by step:
- Load the wide table into Power Query.
- Select only the City column.
- Click Transform › Unpivot Columns › Unpivot Other Columns.
- Rename Attribute to
Monthand Value toOrders. - Click Close & Load. Next month a new Dec column is unpivoted automatically.
स्टेप बाय स्टेप करून बघा:
- Wide table Power Query मध्ये load करा.
- फक्त City column select करा.
- Transform › Unpivot Columns › Unpivot Other Columns वर click करा.
- Attribute चं नाव
Monthआणि Value चं नावOrdersकरा. - Close & Load करा. पुढच्या महिन्यात नवा Dec column आपोआप unpivot होतो.
स्टेप बाय स्टेप करके देखें:
- Wide table को Power Query में load करें.
- सिर्फ़ City column select करें.
- Transform › Unpivot Columns › Unpivot Other Columns पर click करें.
- Attribute का नाम
Monthऔर Value का नामOrdersकरें. - Close & Load करें. अगले महीने नया Dec column अपने आप 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 tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of unpivot on a blank sheet for NovaCraft Tools.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Sana Sheikh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Yuki Tanaka.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply unpivot to a 5-row made-up NovaCraft Tools table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with unpivot.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as NovaCraftTo_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Bengaluru", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
