Features
Converting columns to rows in Power Query—known as unpivoting—reshapes data by turning multiple header columns into attribute-value pairs.
When working with data in Excel, you often receive wide tables where each attribute (for example, a month or a product) is stored in its own column. Reporting tools and downstream analytics, however, frequently require data in a long, tidy format — one column for the attribute name and another for its value. Power Query’s Unpivot feature automates this transformation, letting you convert columns to rows with just a few clicks or a short M script.
Data that is organized with one column per attribute is visually convenient for humans, but it is often inefficient for:
By unpivoting, you normalize the dataset, making it more resilient to structural changes and easier to analyze programmatically.
In Excel, select any cell in your table > Data > From Table/Range. Power Query opens and displays a preview of your data.
Decide which columns should remain fixed (e.g., CustomerID, Product) and which columns need to be converted into rows (e.g., Jan, Feb, Mar sales figures).
Power Query instantly reshapes the table into three columns: the keys you kept, an Attribute column holding former header names, and a Value column containing the data.
Rename Attribute to something descriptive such as Month, and Value to Sales.
Click Home > Close & Load to push the transformed data back to Excel.
let
Source = Excel.CurrentWorkbook(){[Name="tblSales"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(
Source,
{"CustomerID", "Product"},
"Month",
"Sales"
)
in
Unpivoted
This script performs the same operation programmatically, useful for automation or documentation.
Sales column to Decimal Number after transformation.Don’t manually select the columns to unpivot if your dataset may add new periods. Instead, select the stable columns and use Unpivot Other Columns so new headers are automatically included.
After unpivoting, the Sales column may default to Any. Explicitly change it to Decimal Number to avoid errors in aggregations.
If you unpivot columns that should stay wide (e.g., Latitude/Longitude pairs), you end up with redundant rows. Plan your key columns carefully.
A SaaS finance analyst receives monthly revenue by customer with one column per month. By unpivoting, she can build a dynamic PivotChart that automatically incorporates new months when the source file is updated—no extra VBA or manual edits required.
In SQL, you would use UNPIVOT (T-SQL) or UNION ALL/CROSS APPLY techniques to achieve the same shape. While Galaxy’s SQL editor streamlines those operations for database tables, Power Query offers a no-code path for Excel users handling local spreadsheets. If your data eventually lands in a database, the long format produced by Power Query will integrate more cleanly with Galaxy-authored SQL queries and BI pipelines.
Most analytical tools—including PivotTables, Power BI, and SQL databases—prefer data in a long, normalized form. Unpivoting in Power Query eliminates manual reshaping, reduces formula complexity, and ensures your datasets remain scalable as new columns appear. Mastering this technique is essential for anyone who prepares data for reporting or integrates Excel with modern BI or SQL workflows.
Yes. Instead of Unpivot Other Columns, select only the specific headers you want to transform and choose Unpivot Columns. Just remember you’ll need to adjust the selection if new columns are added later.
Use the Pivot Column command. Select the attribute column (e.g., Month) to pivot on, choose the value column, and pick an aggregation method (usually Don’t Aggregate).
No. Power Query works on a copy of the data. The transformed result is loaded to a new worksheet or Data Model, leaving your source intact.
If you used Unpivot Other Columns, Power Query automatically includes the new columns when you refresh. Otherwise, you’ll need to edit the query and add them manually.
.avif){kind=logo}
