Convert Columns to Rows in Excel Power Query

Galaxy Glossary

How do I convert columns to rows using Excel Power Query?

Transforming tabular data by unpivoting multiple columns into attribute–value rows using Power Query.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Description

Table of Contents

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.

Why Convert Columns to Rows in Excel Power Query is important

Normalized, long-format data is easier to aggregate, filter, and visualize. Unpivoting in Power Query automates this normalization so analysts can build robust PivotTables, feed data into Power BI or SQL warehouses, and avoid manual re-work each time new columns appear.

Convert Columns to Rows in Excel Power Query Example Usage



Convert Columns to Rows in Excel Power Query Syntax



Common Mistakes

Frequently Asked Questions (FAQs)

What is unpivoting in Power Query?

Unpivoting is the process of turning multiple columns into two columns—an attribute column containing former headers and a value column containing the corresponding data—thereby converting a wide table into a tall one.

Can I undo an unpivot after closing Power Query?

Yes. Reopen the query, delete or disable the Unpivot step in the Applied Steps pane, and the table snaps back to its original structure.

How does Power Query decide which columns to keep?

The column selection you make determines this. When you choose Unpivot Other Columns, Power Query keeps the unselected (identifier) columns and converts all others.

Does Galaxy help with unpivoting?

Galaxy is a SQL editor, so the transformation would occur in the database layer via the UNPIVOT SQL operator. Galaxy’s AI copilot can generate that SQL for you and share the query with teammates.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.