ExcelGuru

UNPIVOT Columns in Power Query: Convert Cross-Tabs to Tabular Data

Convert cross tab data to tabular data with Power Query tutorial showing data reshaping and unpivoting in Excel
Learn how to transform cross tab reports into clean tabular datasets using Excel Power Query. This practical tutorial explains how to reshape data, unpivot columns, organize records into a structured format, and prepare datasets for PivotTables, dashboards, and reporting. Ideal for Excel users, data analysts, accountants, finance professionals, and anyone who needs to clean complex reports and prepare data for efficient analysis.

Table of Contents

Many reports arrive as a neat grid. Regions run down the side, and months spread across the top. It reads beautifully on paper, so people love it. Yet the moment you try to analyse it, everything jams. A PivotTable cannot read months that live in twelve separate columns. The fix is unpivot, and Power Query makes it a two-click job that flattens any cross-tab into clean, tabular data.

This guide shows how to convert cross-tabs the right way. First, it explains why cross-tabs block analysis. Then it covers unpivot, the robust method, and a real example. Renaming, typing, and a full troubleshooting section follow. By the end, you will flatten any matrix in seconds.

The Cross-Tab Problem

A cross-tab spreads one field across many columns. Months, quarters, or products each get a column. That layout is easy for a human to scan. However, it is the wrong shape for analysis, as below.

Cross-tab reads well but analyses badly; tabular is the fix Cross-tab (wide) RegionJanFebMarEast10012090West8060110 months spread across columns a pivot cannot read this well → Tabular (long) RegionMonthSalesEastJan100EastFeb120EastMar90WestJan80 one row per region and month

Analysis tools expect one column per field. They want a single Month column, not twelve. So a cross-tab must be reshaped first. Tabular data, by contrast, pivots and filters with ease.

What Unpivot Does

Unpivot rotates columns into rows. Each old header becomes a value in a new column. The number underneath moves into a Value column. As a result, wide data becomes long and tidy, as below.

Unpivot turns column headers into rows of data Jan Feb Mar Unpivot AttributeValue Jan100Feb120Mar90 Each old column header becomes a value in a new Attribute column, its number sits in Value.
Headers become data. Unpivot treats Jan, Feb, and Mar as values. So they land in one Attribute column. Then each figure sits neatly in the Value column.

How to Unpivot Columns

The basic move takes only a moment. You select the columns you want to rotate. Then one command flattens them into rows. So a wide block collapses into two clean columns.

Unpivot selected columns: 1. Load the cross-tab into Power Query. 2. Select the month columns, such as Jan to Dec. 3. Right-click a header, then choose Unpivot Columns. 4. Two columns appear: Attribute and Value. The Attribute column holds the old headers. So the Value column holds their numbers.

Use Unpivot Other Columns

There is a smarter, safer version of this. Instead of picking the month columns, pick what to keep. You select only the columns to hold still, like Region. Then Unpivot Other Columns flattens the rest, as below.

Unpivot Other Columns survives a new month Unpivot Columns - you pick Jan, Feb, Mar - a new "Apr" appears - Apr is NOT unpivoted - the query silently misses it Unpivot Other Columns - you pick only Region to keep - everything else unpivots - a new "Apr" appears - Apr is included automatically
The robust method: 1. Select only the columns to keep, such as Region. 2. Right-click, then choose Unpivot Other Columns. 3. Every other column rotates into rows. A new month column next time? It still unpivots. So the query keeps working as your data grows.
Prefer Unpivot Other Columns. It targets the columns to keep, not the ones to rotate. So new columns are caught automatically. That single choice future-proofs your query.

Rename and Set Types

The generic names are the last thing to fix. Attribute and Value describe nothing useful. So rename them to match your data. After that, set the correct data types.

Finish the clean-up: 1. Double-click "Attribute" and rename it to Month. 2. Double-click "Value" and rename it to Sales. 3. Set Month as text and Sales as a number. 4. Close & Load to a table or the Data Model. Correct types prevent errors later on. So your PivotTable sums the Sales cleanly.

A Real Example: Sales Matrix

Picture a sheet of regional sales by month. Regions sit in column A, months across the top. You want a pivot of sales by month and region. Unpivot makes that possible in seconds.

From matrix to analysis: 1. Get Data from the sales matrix sheet. 2. Keep Region, then Unpivot Other Columns. 3. Rename to Month and Sales, then set types. 4. Load and build a PivotTable on the result. Now you can slice sales by any month. So one reshape unlocks the whole report.

Troubleshooting Unpivot

All three problems below are the most common. Each has a clear cause and a quick fix.

Only some columns unpivoted

This usually means you selected the wrong set. Unpivot Columns rotates only the columns you picked. So any header you missed stays as a column. First, undo the step and start again. Then select only the columns you want to keep. Next, choose Unpivot Other Columns instead. That flattens everything else in one clean move.

A new month broke the query

A hard-coded column list is the likely cause. Unpivot Columns names each column it rotates. So a new month falls outside that list. First, replace the step with Unpivot Other Columns. That version keeps only your chosen columns fixed. Everything else rotates, including any new month. After that, fresh columns flow through on refresh.

The values came in as text or errors

Mixed content in the grid causes this. A stray label or blank sits among the numbers. First, check the Value column for odd entries. Then remove or fix those rows in the query. Next, set the Value column to a number type. Power Query will flag any cell that cannot convert. Clean those, and the column becomes fully numeric.

Frequently Asked Questions

  • What does unpivot do in Power Query?+
    It rotates columns into rows. Each header becomes a value in a new column. The figures move into a Value column. So wide cross-tab data becomes tidy and tabular.
  • What is the difference between Unpivot Columns and Unpivot Other Columns?+
    Unpivot Columns rotates the columns you select. Unpivot Other Columns keeps your selection and rotates the rest. So the second option catches new columns too. Therefore it is the safer, future-proof choice.
  • Why should I convert a cross-tab to tabular data?+
    Because analysis tools expect one column per field. A cross-tab spreads one field across many columns. So a PivotTable cannot read it well. Tabular data, by contrast, pivots and filters easily.
  • How do I rename the unpivoted columns?+
    Double-click the Attribute header and type a name. For example, rename it to Month. Then rename Value to something like Sales. Finally, set the correct data types.