Transforming tabular data by unpivoting multiple columns into attribute–value rows using Power Query.
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.
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:
Select any cell in your table » Data > From Table/Range. Power Query opens with your dataset.
Decide which columns contain identifiers (they will stay fixed) and which columns hold values to unpivot. Example: ProductID
stays; Jan
, Feb
, Mar
unpivot.
With the value columns highlighted, right-click » Unpivot Columns. Power Query creates two new columns: Attribute
(old header) and Value
(cell contents).
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.
Click Close & Load. Your tall, query-driven table lands in a new worksheet, ready for PivotTables or Power BI.
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.
Unpivot Other Columns
rather than selecting every future column manually."Unpivot_SalesMonths"
) for clarity.null
with zeros or markers as needed.ProductID
in the selection creates duplicate records. Fix by choosing Unpivot Other Columns instead.attributeColumnName
parameter in custom M or maintain consistent naming via named ranges.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.
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.
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.
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.
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.
Yes. Reopen the query, delete or disable the Unpivot step in the Applied Steps pane, and the table snaps back to its original structure.
The column selection you make determines this. When you choose Unpivot Other Columns, Power Query keeps the unselected (identifier) columns and converts all others.
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.