Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

7.17 Unpivot and Pivot in Power Query with Example

By Last updated:

Start with a problem

Finance sends monthly sales with one column per month. Rani wants one Month slicer and a line chart; with wide columns she needs a new visual change every month.

Explain simply

Unpivot turns many month columns into two columns: Attribute (Month) and Value (Sales). Pivot does the reverse. Unpivot drops null cells.

Show the data

Before (wide):

City Apr-2026 May-2026
Pune 600 250
Nashik 300 500
Kolhapur null 450

About this data: This table uses columns: City, Apr-2026, May-2026. This table uses columns: City, Month, Sales (₹). This table uses columns: City, Jan, Feb, Mar. This table uses columns: City, Month, Orders. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_monthly_sales_wide.csv (source: src/practice/).

Demonstrate

  1. Select City › right-click › Unpivot Other Columns.

    Your result should look like this:

    Rows
    5 (Kolhapur Apr null dropped)

  2. Rename Attribute → Month, Value → Sales. Set Sales to Whole Number.

  3. (Optional) Month › Change Type Using Locale › Date, English (India) after replacing Apr-2026 with 01-Apr-2026 – or add a Month Start column.

    Your result should look like this:

    Month
    Apr-2026, May-2026

  4. To go back: select Month › Transform › Pivot Column › Values: Sales › Sum.

    Your result should look like this:

    Columns
    City, Apr-2026, May-2026

Show the result

Unpivot wide months to a long table, sample layout with fictional data

Your result should look like this (fictional sample):

City Month Sales (₹)
Pune Apr-2026 600
Pune May-2026 250
Nashik Apr-2026 300
Nashik May-2026 500
Kolhapur May-2026 450
Total 2,100

Explain what happened

Unpivot Other Columns keeps City and unpivots every other column, so June will be included automatically next month.

Explore further

  • Unpivot Columns (selected) would miss new months – prefer Unpivot Other Columns.

Detailed reference for this lesson:

Unpivot columns in Power Query with example — in 30 seconds

What: Unpivot turns many month columns into two columns: Month and Value.

Why: Power BI likes long tables. One Month column means one slicer and one line chart for all months.

Tiny example: Jan, Feb and Mar columns become 6 rows with a Month column.

First look at the data (before, then after):

Unpivot columns in Power Query with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Orders typed by month (wide)

City Jan Feb Mar
Pune 100 120 150
Mumbai 200 180 210

Formula / click path

Select City › Transform › Unpivot Other Columns
Rename Attribute, then Month, Value, then Orders

After — After Unpivot (long)

City Month Orders
Pune Jan 100
Pune Feb 120
Pune Mar 150
Mumbai Jan 200
Mumbai Feb 180
Mumbai Mar 210

Do it step by step:

  1. Select only the City column.
  2. Right-click › Unpivot Other Columns. (New month columns next year are then included automatically.)
  3. Rename Attribute to Month.
  4. Rename Value to Orders. Set its type to Whole number.

Friends, 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

  1. Select the column(s) that should stay as they are (City).
  2. Transform › Unpivot Columns › Unpivot Other Columns.
  3. Rename Attribute → Month and Value → Target Orders.
  4. 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.

Once you understand Unpivot, you can "fix" many Excel reports. Do the practice task for sure.

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.

Fix common mistakes

Wrong result Reason Correction
June missing next month Used Unpivot Columns on Apr/May only Use Unpivot Other Columns
Kolhapur April missing Unpivot drops nulls Expected; replace null with 0 first if you need a row
Pivot shows counts Aggregation Count Choose Sum

Practise independently

Practice task

How many rows after unpivot if you first replace Kolhapur's null with 0?

Hints
  • 3 cities × 2 months.
Solution

6 rows; total still ₹2,100.

Think like a professional

Long (tall) tables are what Power BI models expect – unpivoting is one of the most valuable cleaning skills.

Ravi Sir's Cards for this lesson

Unpivot and Pivot. Months as columns cannot be filtered or charted over time easily.Revision cardPivot vs Unpivot. Both reshape a table in Power Query. Unpivot turns columns into rows. Pivot turns rows into columns.Comparison card

मराठी cards