Structuring Data with Unpivot, Pivot, and Column Shaping

Why reshaping matters: wide vs. tall data

Most reporting problems in Excel are not caused by missing formulas; they are caused by data that is shaped in a way that makes analysis fragile. Power Query’s reshaping tools—Unpivot, Pivot, and column shaping—let you convert data into structures that are easier to filter, group, aggregate, and join.

Two common shapes appear in business files:

• Wide (cross-tab): one row per entity (like a product), and many columns for periods or categories (Jan, Feb, Mar… or Region A, Region B…). This is easy for humans to read but hard to scale because every new month becomes a new column.
• Tall (normalized): one row per observation (Product + Month + Value). This is ideal for PivotTables, charts, measures, and consistent refresh because new months become new rows, not new columns.

Unpivot is the main tool for converting wide to tall. Pivot is the main tool for converting tall to wide. Column shaping tools (split, merge, extract, fill, reorder, rename, change type, etc.) help you make the result consistent and analysis-ready.

Unpivot: turning columns into rows

Unpivot takes multiple columns and converts them into two columns: one for the former column headers (often called Attribute) and one for the values (often called Value). This is the fastest way to normalize cross-tab data.

Typical scenario: monthly columns

Imagine a table like this (wide):

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Product | Category | Jan | Feb | Mar | Apr
A100    | Tools    |  10 |  12 |  11 |  14
B200    | Tools    |   8 |   9 |  10 |  11
C300    | Parts    |   4 |   6 |   5 |   7

For analysis, you usually want (tall):

Product | Category | Month | Units
A100    | Tools    | Jan   | 10
A100    | Tools    | Feb   | 12
...
C300    | Parts    | Apr   | 7

Step-by-step: Unpivot monthly columns

In Power Query:

• Select the identifier columns that should remain as-is (here: Product and Category).
• Go to Transform > Unpivot Columns > Unpivot Other Columns. This tells Power Query: keep the selected columns, unpivot everything else.
• Rename the generated columns: rename Attribute to Month, and Value to Units.
• Set data types: Month as Text (or Date if you convert it), Units as Whole Number/Decimal.

Using Unpivot Other Columns is often more robust than selecting all month columns manually, because it automatically adapts if new months appear later. The key is that your identifier columns must be stable and correctly selected.

Unpivot only selected columns

Sometimes you have many non-month columns, and only a subset should be unpivoted. Example:

Product | Category | Currency | Jan | Feb | Mar
A100    | Tools    | USD      | 100 | 120 | 110

Here, you might select Jan–Mar and choose Unpivot Columns. This is less flexible if months change, but it’s appropriate when the unpivot set is fixed (for example, “Budget” and “Actual” columns that never change).

Unpivoting with multiple measures (Units and Revenue)

A common complication is when your wide table has repeated blocks, like Units Jan–Dec and Revenue Jan–Dec. Example:

Product | Jan Units | Feb Units | Jan Revenue | Feb Revenue
A100    | 10        | 12        | 100         | 120

Goal: produce a tall table with columns: Product, Month, Units, Revenue.

One practical approach:

• Unpivot all month-measure columns (everything except Product).
• Split the Attribute column into two parts: Month and Measure (Units/Revenue).
• Pivot the Measure column to create separate Units and Revenue columns.

Step-by-step:

• Select Product and choose Unpivot Other Columns.
• You now have Attribute values like “Jan Units”, “Feb Units”, “Jan Revenue”, etc.
• Select Attribute and use Transform > Split Column > By Delimiter (delimiter is space). Split into two columns: Month and Measure.
• Rename the split columns to Month and Measure.
• Select Measure and choose Transform > Pivot Column. For Values Column, choose the numeric Value column. For aggregation, choose Don’t Aggregate if each Product+Month+Measure is unique; otherwise choose Sum.

This pattern (Unpivot → Split Attribute → Pivot Measure) is extremely useful for turning “stacked headers” into a clean fact table.

Handling blanks, nulls, and zeros after unpivot

After unpivoting, you may see many rows where Value is null because the original wide table had blanks. Decide what those blanks mean:

• Null means “missing”: keep nulls, or filter them out if you only want observed values.
• Blank means “0”: replace null with 0 using Transform > Replace Values or Transform > Replace Errors if needed.

Be careful: replacing null with 0 can change totals and averages. If you’re not sure, keep nulls and let the reporting layer decide how to treat them.

Pivot: turning rows into columns

Pivot takes a column of categories and turns its unique values into new columns. It is the reverse of unpivot. Pivot is useful when you need a cross-tab output for a specific report layout, or when you need to create separate columns for measures like Actual vs Budget.

Typical scenario: category rows to columns

Suppose you have tall data like:

Product | Month | Measure | Amount
A100    | Jan   | Units   | 10
A100    | Jan   | Revenue | 100
A100    | Feb   | Units   | 12
A100    | Feb   | Revenue | 120

You might want:

Product | Month | Units | Revenue
A100    | Jan   | 10    | 100
A100    | Feb   | 12    | 120

Step-by-step: Pivot a Measure column

• Select the column you want to pivot (here: Measure).
• Choose Transform > Pivot Column.
• In the dialog, set Values Column to Amount.
• Choose an aggregation function. If each Product+Month+Measure combination is unique, choose Don’t Aggregate. If there can be multiple rows per combination, choose Sum (or another appropriate aggregation).

The aggregation choice is not just a technical detail; it encodes a business rule. If duplicates exist unexpectedly, “Don’t Aggregate” will error and force you to address data quality. “Sum” will silently combine duplicates, which may be correct or may hide a problem.

Pivoting months into columns (when you truly need wide output)

Sometimes the final consumer expects a wide format (for example, a template that has Jan–Dec columns). You can pivot Month into columns:

• Select Month and choose Pivot Column.
• Values Column: Units (or Amount).
• Aggregation: Sum (common) or Don’t Aggregate (if unique).

Note that pivoting months creates a column per month. If new months appear, Power Query will add new columns on refresh, which may break downstream formulas or formatting. If you need stable columns, consider filtering to a fixed period set or using a calendar table approach in the reporting layer.

Ensuring consistent column order after pivot

Pivot generates columns in an order based on the sorted unique values it encounters. If you want Jan–Dec order, you have options:

• Sort the Month column before pivoting (helps but is not always guaranteed if values are text).
• Convert Month to a real date (e.g., first day of month) and pivot on a formatted month label later.
• After pivot, use Transform > Reorder Columns to enforce a preferred order.

For repeatable refresh, explicit reordering is often the safest if the set of columns is known and fixed.

Column shaping: making the reshaped data usable

Unpivot and Pivot change the structure, but column shaping makes the structure reliable. The goal is to end with columns that have correct names, correct types, consistent values, and keys that can be used for grouping and joining.

Renaming columns strategically

After unpivot, you typically get generic names like Attribute and Value. Rename them immediately to meaningful names (Month, Metric, Amount). This improves readability and reduces confusion when you later reference columns in steps.

Practical tip: if you plan to pivot later, choose names that won’t conflict with pivoted column names. For example, if you will pivot Measure into Units/Revenue columns, keep the numeric column name as Amount until after pivot.

Changing data types at the right time

Data types affect sorting, grouping, and calculations. In reshaping workflows:

• Set types for identifier columns early (Text for IDs, Date for dates).
• For unpivoted Value columns, set type after you’ve handled non-numeric artifacts (like “-” or “N/A”).
• After pivot, verify that the new columns are numeric types; sometimes they become Any due to mixed values.

If you see errors after changing type, inspect the problematic rows and decide whether to replace values, remove rows, or keep as text. Avoid leaving numeric measures as text; it causes subtle issues later.

Splitting columns to extract structure

Splitting is often paired with unpivot because the Attribute column frequently contains multiple pieces of information. Common patterns:

• “2025-01 Units” → split into DatePart and Measure.
• “Region - Product” → split into Region and Product.
• “Dept/CostCenter” → split by delimiter “/”.

Step-by-step example: Attribute values like “2025-01 Revenue”:

• Select Attribute.
• Transform > Split Column > By Delimiter (space).
• Rename to MonthKey and Measure.
• Convert MonthKey to Date by appending “-01” and using Change Type to Date, or keep as text if you will join to a calendar table later.

Merging columns to create keys

After reshaping, you may need a composite key for deduplication or joining. Example: create a key from Product + Month:

• Select Product and Month.
• Transform > Merge Columns.
• Choose a delimiter unlikely to appear in data (e.g., “|”).
• Name it ProductMonthKey.

Use merged keys carefully. They are convenient, but they can hide data issues (like extra spaces). If you merge, consider trimming first.

Trimming and cleaning text to prevent “phantom duplicates”

When you pivot, categories become column headers. If the category values contain trailing spaces or inconsistent casing, you can end up with multiple columns that look similar but are actually different (e.g., “Revenue” vs “Revenue ”). Before pivoting:

• Select the category column (Measure, Month, Region, etc.).
• Transform > Format > Trim.
• Optionally apply Clean to remove non-printing characters.
• Optionally standardize case (Uppercase/Lowercase) if appropriate.

This small step prevents messy outputs and makes pivot results stable.

Filtering and removing columns after reshaping

Reshaping often creates intermediate columns you no longer need (like the original Attribute after splitting). Remove them to keep the model tidy:

• Remove columns that are purely transitional.
• Keep only identifiers, date fields, category fields, and measures needed for analysis.

Also consider filtering out rows that are not real data, such as totals that were embedded in the source (e.g., “Grand Total” rows). If totals are present, remove them before unpivoting when possible; otherwise they will multiply into many rows.

Advanced reshaping patterns you will use repeatedly

Pattern 1: Unpivot with stable identifiers (recommended default)

When you receive a wide table with a few stable ID columns and many changing period columns, the most repeatable approach is:

• Select stable ID columns.
• Unpivot Other Columns.
• Rename Attribute/Value.
• Standardize and type the new columns.

This pattern scales well because new period columns automatically become new rows.

Pattern 2: Unpivot only “value” columns while keeping “descriptor” columns

Sometimes a table has extra descriptor columns that are not stable IDs but should remain (like Currency, Scenario, Version). The key is to decide which columns define the grain (the uniqueness) of a row after reshaping.

Example grain: Product + Currency + Month. If Currency is part of the grain, keep it as an identifier during unpivot. If Currency is constant and irrelevant, you might remove it before unpivoting.

Pattern 3: Pivot for “Actual vs Budget” comparison

Data often arrives tall like:

Dept | Month | Scenario | Amount
IT   | Jan   | Actual   | 1200
IT   | Jan   | Budget   | 1000

To compare easily, pivot Scenario:

• Pivot Scenario.
• Values Column: Amount.
• Aggregation: Sum.

Then you can add a custom column for variance (Actual - Budget) or do it later in Excel. If you add it in Power Query, keep it as a separate step after pivot so the logic is easy to find.

Pattern 4: “Headers in first column” (promote headers + reshape)

Some exports place months across the top but also include extra header rows or merged cells. While the exact cleanup depends on the file, the reshaping sequence often becomes:

• Remove top rows that are not data.
• Promote headers.
• Fill down if needed for grouped labels.
• Unpivot the period columns.

The important part is that unpivot expects a clean header row. If headers are inconsistent, fix them before unpivoting.

Practical walkthrough: from cross-tab to analysis-ready fact table

This walkthrough combines unpivot and column shaping into a repeatable workflow. Suppose you have a sales cross-tab with these columns:

Region | Sales Rep | Product | 2025-01 | 2025-02 | 2025-03
East   | Kim       | A100    | 1000    | 1100    | 1050
East   | Kim       | B200    |  500    |  650    |  600
West   | Sam       | A100    |  900    |  950    | 1000

Goal: Region, Sales Rep, Product, Month (as Date), Sales (as number).

Step-by-step

• Select Region, Sales Rep, Product.
• Transform > Unpivot Other Columns.
• Rename Attribute to Month, Value to Sales.
• Trim Month (in case of hidden spaces).
• Convert Month to Date: if Month is “2025-01”, you can add a custom column like Date.FromText([Month] & "-01"), then remove the original Month and rename the new column to Month. Alternatively, keep Month as text if you prefer.
• Set Sales type to Decimal Number (or Whole Number if appropriate).
• Reorder columns to a logical order: Region, Sales Rep, Product, Month, Sales.

At this point, the table is tall and stable: adding “2025-04” to the source becomes new rows automatically.

Practical walkthrough: turning a long table into a report-friendly layout

Now suppose you have tall data and need a wide output for a specific template:

Region | Month | Scenario | Amount
East   | 2025-01 | Actual | 1000
East   | 2025-01 | Budget |  950
East   | 2025-02 | Actual | 1100
East   | 2025-02 | Budget | 1000

Goal: Region, Month, Actual, Budget.

Step-by-step

• Trim Scenario values (to avoid “Actual ” vs “Actual”).
• Select Scenario > Transform > Pivot Column.
• Values Column: Amount.
• Aggregation: Sum (common for financial data).
• Set data types for Actual and Budget columns to Decimal Number.
• Optional: add a Variance column using Custom Column: [Actual] - [Budget].

If some Region+Month combinations are missing Budget, you will get nulls. Decide whether to keep nulls (recommended) or replace with 0 depending on the reporting requirement.

Common pitfalls and how to avoid them

Pitfall: unpivoting the wrong columns

If you accidentally unpivot an identifier column (like Region), you will multiply rows and lose the ability to group correctly. Use this check: after unpivot, do you still have the columns that define “what a row represents” (the grain)? If not, undo and reselect identifiers.

Pitfall: pivot creates unexpected extra columns

This usually happens because category values are inconsistent (spaces, casing, hidden characters). Trim and clean the pivot column before pivoting.

Pitfall: “Don’t Aggregate” errors during pivot

This indicates duplicates for the pivot key. It can be a data quality issue (same Product+Month+Measure repeated) or a legitimate case (multiple transactions). If legitimate, choose Sum (or another aggregation) and ensure the result matches the business meaning.

Pitfall: month labels sort alphabetically

Text months sort as Apr, Aug, Dec… Convert to a real date or add a numeric month index. If you keep Month as text for display, consider also keeping a MonthStart date column for sorting and relationships.

M code reference: recognizing unpivot and pivot steps

You do not need to write M to use these features, but recognizing the generated steps helps you troubleshoot.

Example: Unpivot Other Columns

= Table.UnpivotOtherColumns(Source, {"Product", "Category"}, "Month", "Units")

Example: Pivot Column with Sum

= Table.Pivot(Source, List.Distinct(Source[Scenario]), "Scenario", "Amount", List.Sum)

If your pivot column contains many distinct values, the pivot step will create many columns. That can be correct (e.g., scenarios) or a sign that the column should be cleaned or grouped first.