Convert Columns to Rows in Excel Power Query

Overview

Converting columns to rows—commonly called unpivoting—is a core data-shaping task in Excel Power Query. The operation restructures wide tables (many columns) into tall, normalized tables (fewer columns, more rows) by turning each column header into a row value. This makes downstream analysis, aggregation, and visualization far easier.

Why Converting Columns to Rows Matters

Most analytics tools, including Excel PivotTables, Power BI, and SQL engines, prefer data in a normalized, row-oriented shape. Wide tables often emerge from manual data entry or exported reports (Jan | Feb | Mar | …). While readable for humans, that layout is hard to filter, join, or aggregate programmatically. Unpivoting accomplishes:

• Scalability – new months, departments, or metrics appear as additional rows instead of requiring schema changes.
• Compatibility – normalized data feeds smoothly into BI tools and databases.
• Data quality – reduces redundancy and enforces a single source of truth per attribute.

Step-by-Step Guide: Unpivot in Power Query

1. Load Data into Power Query

Select any cell in your table » Data > From Table/Range. Power Query opens with your dataset.

2. Identify the Key Columns

Decide which columns contain identifiers (they will stay fixed) and which columns hold values to unpivot. Example: ProductID stays; Jan, Feb, Mar unpivot.

3. Apply Unpivot Columns

With the value columns highlighted, right-click » Unpivot Columns. Power Query creates two new columns: Attribute (old header) and Value (cell contents).

4. Rename and Set Data Types

Rename Attribute to something meaningful (e.g., Month) and Value to SalesAmount. Then set proper data types— usually Text for the attribute and Decimal Number or Currency for the value.

5. Load Back to Excel

Click Close & Load. Your tall, query-driven table lands in a new worksheet, ready for PivotTables or Power BI.

Behind the Scenes: Understanding the M Code

When you unpivot, Power Query generates M code similar to:

// Sample generated by Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
Unpivot = Table.UnpivotOtherColumns(
Source,
{"ProductID"}, // columns to keep
"Month", // new header column
"SalesAmount" // new value column
)
in
Unpivot

Table.UnpivotOtherColumns keeps ProductID intact and converts every other column into a Month/SalesAmount pair.

Best Practices

• Keep immutable keys fixed. Use Unpivot Other Columns rather than selecting every future column manually.
• Apply proper data types early. Downstream steps—filters, merges, aggregations—depend on correct types.
• Document each applied step. Rename steps in the APPLIED STEPS pane ("Unpivot_SalesMonths") for clarity.
• Handle blanks explicitly. Replace null with zeros or markers as needed.

Common Mistakes and How to Fix Them

1. Unpivoting Identifier Columns: Accidentally including keys like ProductID in the selection creates duplicate records. Fix by choosing Unpivot Other Columns instead.
2. Forgetting Data Types: Neglecting to set numeric types makes sums return text errors. Always use Transform > Data Type.
3. Refresh Failures After Column Rename: If source columns change names, Power Query errors. Use the optional attributeColumnName parameter in custom M or maintain consistent naming via named ranges.

Real-World Example: Monthly Sales Sheet

Suppose you receive a sales file each month with the layout:

Product | Jan | Feb | Mar
A | 120 | 95 | 110
B | 210 | 188 | 199

After unpivoting, the resulting table becomes:

Product | Month | Sales
A | Jan | 120
A | Feb | 95
A | Mar | 110
B | Jan | 210
B | Feb | 188
B | Mar | 199

With this tidy format, you can build a PivotTable that slices by month, compute year-to-date totals, or merge with product lookup tables.

Achieving the Same Result with SQL and Galaxy

If your dataset lives in a relational database, you can unpivot directly via SQL. Tools like Galaxy—a modern, AI-assisted SQL editor—make this simple with contextual autocomplete and code generation. An ANSI-compliant equivalent looks like:

SELECT ProductID,
Month,
SalesAmount
FROM Sales
UNPIVOT (
SalesAmount FOR Month IN ([Jan], [Feb], [Mar])
) AS unpvt;

Galaxy’s AI Copilot can suggest the UNPIVOT syntax based on your table schema, then let you save the query to a shared Collection for team reuse—eliminating copy-paste headaches across Slack or Notion.

Conclusion

Unpivoting in Power Query turns cumbersome, wide spreadsheets into analytics-ready datasets. Mastering the technique—alongside best practices such as keeping identifier columns fixed, applying data types, and documenting steps—will streamline your reporting pipelines. Whether you operate in Excel or a SQL editor like Galaxy, understanding how to convert columns to rows is foundational to reproducible, scalable data work.