Cross-tab reports look great in Excel but break every analysis tool that touches them. Learn how to use Power Query's unpivot operations to reshape wide, matrix-formatted data into tidy, analysis-ready tables — including how to handle multi-row headers, merged cell hierarchies, and dynamic column counts.
If you've spent any time working with real-world Excel files, you've met the enemy: a beautifully formatted cross-tab report where months run across the top, product categories cascade down the left, and the actual data lives in a grid of cells that no pivot table, DAX measure, or SQL join can touch without surgery. These reports look great in a printed board pack. They're analysis poison.
The good news is that Power Query was practically built to solve this problem. Unpivoting — the process of collapsing wide column-based data into tall row-based data — is one of the most transformative operations you'll perform as a data practitioner. Master it, and you unlock an enormous class of Excel reports that were previously unusable. By the end of this lesson, you'll be able to handle simple single-header cross-tabs, multi-row header matrices, and partially structured reports where row labels are themselves nested — all without breaking a sweat when next month's file arrives.
What you'll learn:
This lesson assumes you're comfortable with the Power Query Editor interface, know how to connect to an Excel workbook, and understand the basic connect-transform-load workflow. You should know what promoting headers means and have a working grasp of how steps chain together in the Applied Steps pane. If you're newer to M or the query editor, Getting Started with Power Query will bring you up to speed.
Cross-tabulations are a natural way for humans to read summarized data. When a finance analyst sends you a spreadsheet with regions as row headers and months as column headers and revenue figures in every cell, they've made something easy to scan visually. Each cell sits at the intersection of two dimensions, and the eye moves across the grid intuitively.
The problem is that this structure encodes two dimensions of information in the layout of the table rather than in the data itself. The month "January" isn't stored as a value in a column — it's implied by which column a number lives in. This breaks every standard data operation:
The solution is to reshape the data into what statisticians call tidy format: every variable in its own column, every observation in its own row. For a cross-tab report, that means turning the column headers (the months) into values in a "Month" column, and stacking all the revenue figures into a single "Revenue" column. This is exactly what unpivoting does.
Key insight
The fundamental rule of tidy data is that each column represents one variable and each row represents one observation. A cross-tab violates this by using column position to encode a variable's value. Unpivoting restores the rule.
Before you open Power Query, spend two minutes studying your source file. The answers to four questions determine which unpivot strategy you'll use:
1. How many header rows are there? A single header row is the simplest case. Two or more header rows — where, say, the top row shows the quarter and the row below shows the month — require extra work before you can unpivot.
2. Are the row labels in a single column or hierarchical across multiple columns? If product category, subcategory, and product name each live in their own column, you're in the simple case. If the category appears in one column and subcategory in a merged cell below it (a common Excel aesthetic choice), you'll need Fill Down first.
3. Are there summary/total rows? Subtotals and grand totals embedded in the data look like regular rows to Power Query. You need to remove them before unpivoting or they'll corrupt your fact data.
4. Does the number of columns change between refreshes? If new months are added to the right each period, hardcoding which columns to unpivot will break your pipeline. You'll need a dynamic approach using Unpivot Other Columns.
Take a mental snapshot of these four characteristics before proceeding. The rest of this lesson walks through each scenario in increasing complexity.
Let's start with the most common case. You receive a monthly sales report from a regional sales manager. It looks like this in Excel:
Product | Jan-24 | Feb-24 | Mar-24 | Apr-24
-----------------+---------+---------+---------+--------
Widget A | 12,400 | 11,900 | 14,200 | 13,800
Widget B | 8,750 | 9,100 | 8,400 | 9,650
Gadget Pro | 22,100 | 21,500 | 23,800 | 24,200
Gadget Lite | 15,300 | 14,900 | 16,100 | 15,700
One header row, one identifier column, four value columns. This is as clean as cross-tabs get.
Connect to the workbook via Data > Get Data > From File > From Excel Workbook. Navigate to the correct sheet and click Transform Data to open the Power Query Editor. If you need a refresher on connecting to Excel files, the lesson on connecting to Excel workbooks and named ranges covers this thoroughly.
After promoting headers (if Power Query hasn't already done so), your query table will show Product as a text column and Jan-24 through Apr-24 as separate columns.
Select the Product column by clicking its header. Then in the Transform ribbon, click the dropdown arrow next to Unpivot Columns and choose Unpivot Other Columns.
This is the critical choice. "Unpivot Other Columns" tells Power Query: "Whatever columns I've selected are identifier columns — keep them as they are. Take everything else and unpivot it." This is almost always the right choice for cross-tabs, because it means that when new month columns appear in next month's file, they'll automatically get unpivoted without any change to your query.
After the step executes, your table transforms from 4 rows × 5 columns to 16 rows × 3 columns:
Product | Attribute | Value
------------+-----------+-------
Widget A | Jan-24 | 12400
Widget A | Feb-24 | 11900
Widget A | Mar-24 | 14200
Widget A | Apr-24 | 13800
Widget B | Jan-24 | 8750
...
Power Query names the new columns "Attribute" and "Value" by default. Rename them to something meaningful — "Month" and "Revenue" — by double-clicking each header.
Tip
Always rename "Attribute" and "Value" immediately after unpivoting. These generic names have a way of surviving into your data model and confusing every person who opens the file six months later. Rename them in the same session while the context is fresh.
Finally, change the data type of the Revenue column to Decimal Number or Whole Number (not Text), and if your month values are in a format Power Query can parse, consider converting them to proper dates using the date transformation techniques covered elsewhere in this learning path.
The M code behind this entire operation is straightforward:
let
Source = Excel.Workbook(File.Contents("C:\Reports\Sales_Apr2024.xlsx"), null, true),
Sheet1 = Source{[Item="SalesData",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet1, [PromoteAllScalars=true]),
UnpivotedColumns = Table.UnpivotOtherColumns(
PromotedHeaders,
{"Product"},
"Month",
"Revenue"
),
ChangedType = Table.TransformColumnTypes(
UnpivotedColumns,
{{"Revenue", type number}}
)
in
ChangedType
Notice how Table.UnpivotOtherColumns takes the table, a list of columns to keep as identifiers, and the names for the new Attribute and Value columns. This is the M function you'll use most often in unpivot scenarios.
Real reports are rarely as clean as Scenario 1. More often, the row structure carries multiple dimensions. Consider this sales report with both Region and Product as row identifiers:
Region | Product | Jan-24 | Feb-24 | Mar-24
----------+-------------+---------+---------+--------
North | Widget A | 4,200 | 3,900 | 4,800
North | Widget B | 2,100 | 2,400 | 2,200
South | Widget A | 3,800 | 3,600 | 4,100
South | Widget B | 2,900 | 3,100 | 2,800
The fix here is identical to Scenario 1, except you select both Region and Product before clicking Unpivot Other Columns:
UnpivotedColumns = Table.UnpivotOtherColumns(
PromotedHeaders,
{"Region", "Product"},
"Month",
"Revenue"
)
Both identifier columns are preserved in every row of the output. The result is 12 rows, each with Region, Product, Month, and Revenue.
Now for the common complication: the Excel file uses merged cells to show the hierarchy visually:
Region | Product | Jan-24 | Feb-24 | Mar-24
----------+-------------+---------+---------+--------
North | Widget A | 4,200 | 3,900 | 4,800
| Widget B | 2,100 | 2,400 | 2,200
South | Widget A | 3,800 | 3,600 | 4,100
| Widget B | 2,900 | 3,100 | 2,800
When Power Query reads this, the merged cells become nulls. The Region column will contain "North", null, "South", null instead of repeating the value. If you unpivot now, your output rows for Widget B will have null in the Region column, which breaks every join and filter downstream.
The fix is Fill Down, applied before unpivoting. Select the Region column, then go to Transform > Fill > Down. Power Query propagates each non-null value downward until it hits the next non-null value. After Fill Down, the Region column correctly reads North, North, South, South.
Warning
Always apply Fill Down before removing rows or unpivoting. If you remove blank rows or filter the data first, you may lose the anchor values that Fill Down needs to propagate. Get the structure right before you start slicing.
The lesson on reshaping data with transpose, fill down, and fill up covers Fill Down in detail if you need a deeper treatment.
This is where unpivoting gets genuinely tricky. Many management reports use two or more header rows to organize the column dimension. A quarterly-then-monthly layout looks like this:
Row 1: | Q1 2024 | Q2 2024
Row 2: Product | Jan-24 | Feb-24 | Mar-24 | Apr-24 | May-24 | Jun-24
--------+----------+--------+--------+--------+--------+--------+--------
Widget A | 12,400 | 11,900 | 14,200 | 13,800 | 15,100 | 14,700
Power Query sees this as a table where the first two rows are data rows (not headers), and the first column has no header. You need to merge these two header rows into a single meaningful column header before you can unpivot.
Step 1: Don't promote headers yet. When you load the data, let Power Query treat everything as data rows. The default Column1, Column2, Column3 names are fine for now.
Step 2: Extract the first two rows. Use Table.ToRows to capture those header rows, or more practically, use the approach of converting them to lists and then combining them:
let
Source = Excel.Workbook(File.Contents("C:\Reports\Quarterly_Sales.xlsx"), null, true),
Sheet1 = Source{[Item="QuarterlySales",Kind="Sheet"]}[Data],
// Extract the two header rows as lists
Row1 = Table.ToRows(Table.FirstN(Sheet1, 1)){0},
Row2 = Table.ToRows(Table.FirstN(Sheet1, 2)){1},
// Combine them: "Q1 2024 | Jan-24", "Q1 2024 | Feb-24", etc.
// The first column is an identifier, leave it as-is
CombinedHeaders = List.Transform(
List.Positions(Row1),
each if _ = 0
then "Product"
else (if Row1{_} = null then "" else Text.From(Row1{_}))
& " | "
& Text.From(Row2{_})
),
// Remove the two header rows from the data
DataOnly = Table.Skip(Sheet1, 2),
// Apply the combined headers
RenamedTable = Table.RenameColumns(
DataOnly,
List.Zip({Table.ColumnNames(DataOnly), CombinedHeaders})
),
// Now unpivot
Unpivoted = Table.UnpivotOtherColumns(
RenamedTable,
{"Product"},
"Quarter_Month",
"Revenue"
)
in
Unpivoted
After unpivoting, your "Quarter_Month" column contains values like "Q1 2024 | Jan-24". You can then split this column on the delimiter to create separate Quarter and Month columns, or just parse out the month if the quarter is redundant.
Key insight
The pattern for multi-row headers is always: (1) capture the header rows as lists before they're treated as data, (2) combine them into single meaningful strings, (3) remove the header rows from the data body, (4) rename the columns with your combined strings, (5) then unpivot. Trying to skip any of these steps creates hard-to-debug column name collisions.
Step 3: Fill gaps in the top header row. Often the top header row uses merged cells, so "Q1 2024" appears once and then the next two cells are null. You need to fill those nulls before combining the headers. Convert Row1 to a table, Fill Down, then convert back to a list:
// Fill the merged-cell gaps in Row1
Row1Filled = List.RemoveNulls(
Table.Column(
Table.FillDown(
Table.FromList(Row1, Splitter.SplitByNothing(), {"Val"}),
{"Val"}
),
"Val"
)
),
Actually, the cleaner approach here is to handle the top header row as a mini table and apply Table.FillDown to it before combining with Row2.
Production reports almost always contain total rows — subtotals by region, grand totals at the bottom, running totals mid-table. These don't just add noise; they actively corrupt your analysis by double-counting values when you aggregate.
Consider a report where every region block ends with a "Region Total" row:
Region | Product | Jan-24 | Feb-24 | Mar-24
----------+--------------+--------+--------+--------
North | Widget A | 4,200 | 3,900 | 4,800
North | Widget B | 2,100 | 2,400 | 2,200
North | Region Total | 6,300 | 6,300 | 7,000
South | Widget A | 3,800 | 3,600 | 4,100
South | Widget B | 2,900 | 3,100 | 2,800
South | Region Total | 6,700 | 6,700 | 6,900
| Grand Total | 13,000 | 13,000 | 13,900
Filter these out before applying Fill Down or Unpivot. In Power Query, filter the Product column to exclude values containing "Total":
FilteredRows = Table.SelectRows(
FilledDown,
each not Text.Contains(Text.From([Product]), "Total")
)
Warning
Be careful with filter logic like not Text.Contains([Product], "Total") — it will also remove any legitimate product that happens to have "Total" in its name (imagine a product called "Total Control Valve"). Build your filter to be as precise as possible, using exact matches where you can, or confirm with the data owner that no product names overlap with the summary row labels.
You can also filter on null values in the data columns if the grand total row is identifiable that way, or add a step that removes the last N rows if the total is always at the bottom.
Sometimes you have a wide table where some columns should stay as columns (they're genuine variables) and only a subset should be unpivoted. Consider a customer analytics report:
Customer_ID | Region | Segment | Jan_Spend | Feb_Spend | Mar_Spend | Lifetime_Value | Credit_Score
Here, Lifetime_Value and Credit_Score are customer attributes — they shouldn't become rows. Only the monthly spend columns should be unpivoted. If you use "Unpivot Other Columns" with just Customer_ID, Region, and Segment as identifiers, you'd inadvertently unpivot Lifetime_Value and Credit_Score too.
The right approach is to either:
Option 2 is more robust because it's driven by what you want to keep, not what you want to unpivot. If a new month column appears next refresh, it'll automatically get unpivoted. But if someone adds a new attribute column like "Churn_Risk_Score," it'll also automatically get unpivoted — which is wrong. In this case, you'll need a naming convention strategy: prefix all time-series columns with a consistent pattern and use a custom M step to identify columns dynamically.
Here's how to unpivot all columns matching a pattern:
let
// Identify columns that are monthly spend (match pattern Mon_Spend)
SpendColumns = List.Select(
Table.ColumnNames(Source),
each Text.EndsWith(_, "_Spend")
),
// Everything else is an identifier
IdentifierColumns = List.Difference(
Table.ColumnNames(Source),
SpendColumns
),
Unpivoted = Table.UnpivotOtherColumns(
Source,
IdentifierColumns,
"Month",
"Spend"
)
in
Unpivoted
This pattern — dynamically computing identifier columns as the complement of your target columns — is production-grade. It handles both new month columns and new attribute columns correctly, as long as your naming convention holds.
Tip
Establish a column naming convention with whoever produces the source report. If all time-series columns end with _Spend, _Units, or _Budget, your unpivot logic can key off that pattern and become genuinely self-maintaining. This conversation is worth having before you build the pipeline.
A one-time unpivot is a party trick. A pipeline that handles monthly refreshes without breaking is a professional tool. Here's what that looks like in practice.
If the source file path changes (new folder, new server, new SharePoint location), hardcoding the path in your query is a maintenance burden. Use a parameter or a named range to store the path. The lesson on parameterized queries and dynamic data sources covers this in depth.
Don't put everything in one query. Break your pipeline into stages:
This separation means that when something breaks, you can open the Raw query and see exactly what arrived from the source — without wading through twelve transformation steps. The lesson on multi-stage staging architectures describes this pattern in detail.
After unpivoting, some rows may have null in the value column. This happens when a cell was blank in the original cross-tab. By default, Table.UnpivotOtherColumns drops null-value rows entirely. This is usually the right behavior — you don't want rows that say "Widget A, March, null revenue." But confirm this is your intent. If you need to preserve null rows (to track missing data explicitly), use Table.Unpivot with the MissingField.UseNull option or add the nulls back with a custom step.
If you're combining multiple reports from a folder (a very common pattern), null handling becomes especially important. The lesson on combining files from a folder discusses how to apply transformation templates consistently across multiple files.
The scenario: You receive a quarterly budget vs. actual report from your finance team as an Excel file. The file has two header rows (the first row shows "Budget" or "Actual," the second shows the month), a "Department" column using merged cells for the department hierarchy, and a "Total" row at the bottom of each department block.
Your task: Build a Power Query pipeline that produces a tidy output table with columns: Department, Cost_Center, Metric_Type (Budget or Actual), Month, Amount.
Step 1: Load the file and examine the raw data in Power Query. Count the header rows and identify the merged cell pattern in the Department column.
Step 2: Without promoting headers, extract the first two rows to build your combined column headers. For the "Department" and "Cost_Center" columns, use their actual names. For every value column, combine the metric type (Row 1) and month (Row 2) with a " | " separator.
Step 3: Remove the two header rows from the data body and rename columns with your combined headers.
Step 4: Fill Down the Department column to resolve the merged cell nulls.
Step 5: Filter out rows where Cost_Center contains "Total" or is null.
Step 6: Use Table.UnpivotOtherColumns with {"Department", "Cost_Center"} as identifiers, naming the output columns "Metric_Month" and "Amount."
Step 7: Split the "Metric_Month" column on " | " to create separate "Metric_Type" and "Month" columns.
Step 8: Set data types — Department and Cost_Center as Text, Metric_Type as Text, Month as Text (you'll convert to Date separately), Amount as Decimal Number.
Verification: Your final table should have exactly (number of cost centers × number of months × 2 metrics) rows. If you have 8 cost centers, 6 months, and 2 metrics (Budget/Actual), you should have 96 rows. Count them. If you have more, you have total rows leaking through. If you have fewer, null values are being dropped that you expected to keep.
This is the classic sign that you used Unpivot Columns (the hardcoded variant) instead of Unpivot Other Columns. When you use Unpivot Columns, Power Query records the specific column names to unpivot. When new columns appear, they aren't in the list and stay as columns — or worse, the step errors because expected columns are missing.
Fix: Switch to Table.UnpivotOtherColumns and specify only your identifier columns. The function will capture whatever value columns exist at refresh time.
You promoted headers when you shouldn't have, or the source data's first row isn't actually a header. Power Query assigned default column names (Column1, Column2, etc.) because the header row had nulls or the promotion step ran on data that wasn't ready.
Fix: Don't promote headers until you've verified what the first row actually contains. Check the Raw query step first. If your source has multiple header rows, don't promote — handle the headers manually as described in Scenario 3.
Likely cause: total rows, blank rows, or repeated header rows embedded in the data. Cross-tab reports frequently re-print column headers partway through the data for printing purposes. These rows unpivot into garbage data like ("Total", "Jan-24", "Grand Total").
Fix: Before unpivoting, filter out rows where the identifier column contains header-like values. A filter of Table.SelectRows(t, each [Department] <> "Department" and [Department] <> null) removes re-printed headers and blank rows in one step.
Note
When something unexpected happens after unpivoting, the best diagnostic strategy is to remove the Unpivot step temporarily and examine what the table looks like immediately before the step. Add a filter step to look at just the rows that are causing problems. Nine times out of ten, you'll spot a total row or repeated header row immediately.
The source Excel file likely stored numbers as text (a formatting issue common in exported reports), or Power Query read the column as text before it understood the values were numeric.
Fix: After unpivoting, use Transform > Data Type > Decimal Number to change the type. If that produces errors because of commas or currency symbols, clean the value column first: Text.Remove([Revenue], {",","$","£","€"}) before converting. The lesson on cleaning messy data with nulls, errors, and type conversions covers this pattern thoroughly.
This happens when you apply Fill Down to a column that has actual legitimate nulls mixed in with the merged-cell nulls. The function can't distinguish between "this null is a merged cell" and "this null is a genuinely missing value."
Fix: If you have a reliable way to identify group boundaries (a subtotal row, a blank row, a row number pattern), filter or mark those boundaries before applying Fill Down. Alternatively, fill down and then verify that the results are correct by counting the rows per group and comparing to expectations.
Unpivoting is a relatively lightweight operation within Power Query — it's essentially a table restructuring that doesn't require sorting, grouping, or external data lookups. However, some surrounding operations deserve attention.
If you're applying unpivot as part of a folder combine pattern where you're stacking together 24 monthly files, each with its own unpivot transformation, the total operation scales linearly with file count. This is usually fine for monthly batch reports, but if you're dealing with hundreds of files, check whether query folding is possible at the source or whether buffering intermediate results makes sense. The lesson on Power Query performance and folding has detailed guidance.
The dynamic column detection pattern (using List.Select to find columns by name pattern) adds a small overhead because Power Query must retrieve the column names before it can execute the unpivot. In most real-world scenarios this is negligible, but be aware of it if you're chaining many such dynamic steps in a complex pipeline.
It's worth noting that unpivoting is reversible. After you've reshaped the data into tidy format for analysis, you may need to re-create a cross-tab layout for a presentation report. Power Query's Pivot Column operation (Transform > Pivot Column) does exactly this — it takes a column of values and spreads them into new columns. The lesson on generating cross-tabulated reports in Power Query covers that direction in detail.
The professional pattern is: unpivot at the ingestion layer, pivot only at the presentation layer. Keep your analytical tables in tidy format throughout your data pipeline. Reshape for display only at the last step, and preferably in a separate query that references the tidy source rather than transforming the analytical table in place.
You've now covered the full spectrum of unpivot scenarios that appear in real Excel-based data pipelines:
Table.UnpivotOtherColumns with explicit identifier columnsList.Select to build refresh-safe pipelinesThe underlying mental model is consistent across all of them: identify what stays (your identifier columns), identify what transforms into rows (your value columns), clean everything that could interfere (totals, nulls, merged headers), and then execute the unpivot. Everything else is application of that model to whatever messy reality the source file presents.
Where to go next:
Unpivoting is one of those skills that pays for itself immediately. The day you can open a cross-tab report from finance, reshape it in four steps, and have it feeding a live dashboard — while your colleagues are still copying and pasting — is a good day.