Unpivoting Columns to Rows in Excel with Power Query: A Step-by-Step Guide

If you've ever received data where months, categories, or regions are spread across multiple columns, you know the pain: charts break, PivotTables struggle, and analysis grinds to a halt. Power Query's Unpivot feature solves this in seconds — no formulas, no VBA, no manual copy-pasting.

What Is Unpivoting (and Why Does It Matter)?

Unpivoting transforms data from wide format (many columns) to long format (many rows). It's the opposite of a PivotTable — instead of summarizing rows into columns, you're expanding columns back into rows.

Consider a sales table where each month is its own column: Product, Region, January, February, March. After unpivoting, it becomes four clean columns: Product, Region, Month, Sales. Every month gets its own row instead of its own column.

Why does this matter? Long format is the standard for databases, Power BI, and most analytical tools. Once your data is in long format, PivotTables, charts, and SUMIFS all become dramatically easier to build and maintain.

When Should You Unpivot?

Unpivoting is the right move when you have time-period columns (months, quarters, or years spread across columns), region or category headers used as column names, survey data where each question is a column and you need responses as rows, or when you need a key-value structure to join with another dataset.

Don't unpivot if your columns represent genuinely different attributes like Name, Age, and Email. Those are separate fields — they should stay as columns.

Step-by-Step: Unpivoting in Power Query

Step 1 — Load your data into Power Query

Select any cell inside your data range. Go to the Data tab on the ribbon and click From Table/Range. If your data isn't already formatted as a Table, Excel will prompt you to convert it — click OK. Power Query Editor will open in a new window with your data loaded.

Step 2 — Select the columns you want to keep

Hold Ctrl and click on the identifier columns — the ones that should stay as-is, like Product and Region. These are the columns that will not be unpivoted. This is the most important step: if you select the wrong columns, your output will be wrong. Think of it as selecting what stays, not what transforms.

Step 3 — Run the Unpivot

With your identifier columns selected, right-click any selected column header. In the context menu choose Unpivot Other Columns. This transforms all unselected columns into rows. You'll also see "Unpivot Only Selected Columns" and plain "Unpivot Columns" — more on those below.

Step 4 — Rename the new columns

Power Query creates two new columns named Attribute and Value by default. Double-click each header to rename them to something meaningful — in this example, Month and Sales.

Step 5 — Set data types and load to Excel

Check that your Sales column has the correct data type. Click the type icon in the column header and select Whole Number or Decimal Number as needed. Then click Close & Load in the top-left of the Editor. Your transformed table appears in a new Excel sheet.

Tip: Because this is a Power Query connection, when your source data changes, click Data → Refresh All and the unpivoted table updates instantly — no need to redo the steps.

Three Unpivot Methods Explained

Power Query offers three variations, and choosing the right one matters.

Unpivot Columns transforms only the columns you explicitly selected. Use this when you know exactly which columns to unpivot and that set won't change.

Unpivot Other Columns transforms every column except the ones you selected. This is the recommended option for most real-world scenarios. If your source gains a new month column (say, April), this method automatically includes it when you refresh.

Unpivot Only Selected Columns is the same as the first option but accessed from the Transform tab. It hardcodes the selected column names in the M code, so new columns added later will be silently ignored on refresh.

The M Code Behind the Magic

Every action in Power Query generates M code behind the scenes. Click View → Advanced Editor to see it. Here's what the unpivot step looks like:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source, {
        {"Product", type text},
        {"Region", type text}
    }),
    UnpivotedColumns = Table.UnpivotOtherColumns(
        ChangedType,
        {"Product", "Region"},
        "Month",
        "Sales"
    ),
    FinalTypes = Table.TransformColumnTypes(UnpivotedColumns, {
        {"Sales", type number}
    })
in
    FinalTypes

The key function is Table.UnpivotOtherColumns(). It takes four arguments: the table, the list of columns to keep, the name for the new Attribute column, and the name for the new Value column.

Pro Tips & Common Mistakes

Always convert your range to a Table first. Named Tables (Ctrl+T) give Power Query a stable reference. If the source is just a range, adding rows later might not be picked up correctly on refresh.

Promote headers before unpivoting. Column names become the Attribute values after unpivoting — so correct, meaningful headers are essential before you start.

Remove blank rows before transforming. Add a Remove Blank Rows step (Home → Remove Rows → Remove Blank Rows) before unpivoting to avoid nulls in your output.

Don't select the wrong columns. If you accidentally select a value column as an identifier, that column gets treated as an ID and everything collapses incorrectly. Always double-check you've selected only the "stay as-is" columns.

Always rename Attribute and Value. Leaving default names makes your output confusing and harder to use in PivotTables or formulas downstream.

Handle nulls after unpivoting. If some source cells were blank, they'll appear as null in the Value column. Filter them out with Home → Remove Rows → Remove Blank Rows after the unpivot step.

Summary

Unpivoting turns wide, hard-to-analyze data into a clean long format that works with PivotTables, charts, Power BI, and everything else. Load your data via Data → From Table/Range, select your identifier columns, choose Unpivot Other Columns, rename the output columns, set data types, and load. Use Refresh All whenever your source data changes — Power Query handles the rest automatically.