Power Query Problems

How to Unpivot Excel Data for PivotTables Using Power Query

Steven Parker Oct 13, 2026 869 views

Question details

The user needs to reshape and normalize data (Event, Date, Account, Value) using Power Query to avoid combined columns and null values, enabling proper use of PivotTables and slicers.

How to Unpivot Excel Data for PivotTables and Slicers with Power Query

Product: Microsoft Excel (Power Query)
Device & OS: not provided
Scenario: Transforming poorly structured source data into a flat, normalized table format for data analysis and visualization.
Observed behavior: Standard unpivoting creates combined columns or null account values, preventing PivotTables and slicers from functioning correctly.

Before you start

Ensure your source data is organized in a clear tabular format without merged cells. It is highly recommended to convert your raw data range into an official Excel Table (Ctrl+T) before importing it into Power Query.

Solution 1Recommended

Correctly Unpivot Only the Value Columns in Power Query

The most effective approach to normalizing data is selecting your static category columns and unpivoting the rest.

To prevent category fields from merging and creating null values, you must explicitly tell Power Query to unpivot only the columns containing your measurable values, while keeping categories (like Date, Event, and Account) intact.

Load Data into Power Query

Select any cell inside your data table, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to launch the Power Query Editor.

Select Category Columns

Hold down the 'Ctrl' key and click the headers of the columns that should remain as fixed categories (e.g., Event, Date, Account).

Unpivot Other Columns

Right-click the header of one of your selected category columns and choose 'Unpivot Other Columns' from the context menu.

Rename the Unpivoted Columns

Double-click the new 'Attribute' column header and rename it to represent the category (e.g., 'Revenue Stream'), then rename the 'Value' column to your metric name (e.g., 'Amount').

Load the Normalized Table

Click the 'Close & Load' button at the top left to output the normalized table into a new worksheet.

Correctly Unpivot Only the Value Columns in Power Query

Data Normalization Complete: Your data is now in a flat, one-dimensional format, making it fully compatible with PivotTables and slicers without generating null rows.

Solution 2

Create a PivotTable and Add Slicers from the Unpivoted Data

Once the data is normalized via Power Query, you can use it to build dynamic, filterable reports.

Free Microsoft Office alternative

Use WPS Spreadsheet for Seamless Data Analysis

While Power Query is a Microsoft-specific tool, WPS Office provides a free, lightweight, and highly compatible alternative for everyday data analysis. You can easily create dynamic PivotTables, insert slicers, and filter large datasets with a familiar user interface that requires no learning curve.

Fully compatible with Microsoft Excel (.xlsx) files and standard PivotTable structuresBuilt-in PivotTable and Slicer tools for dynamic data visualization without complex codingLightweight application that runs smoothly even with large, multi-sheet datasetsFree to use with a highly familiar user interface, ensuring a seamless migration from Microsoft Office

microsoft office alternative - wps office

Frequently Asked Questions

Why does unpivoting in Power Query create null values in my account column?

This usually happens if you try to unpivot all columns or handle subcategories incorrectly. To fix this, select only your static category columns (like Date and Event), right-click, and select 'Unpivot Other Columns' instead of unpivoting the entire dataset.

Can I use slicers on data that hasn't been unpivoted?

While you can technically add slicers to un-normalized data, they will only filter the specific column they are tied to. If your values are spread across multiple columns (e.g., one column per month), a single slicer won't be able to filter the data globally. Unpivoting normalizes the data into a single dimension, allowing slicers to work perfectly.

How do I update my PivotTable when the original source data changes?

First, go to the Data tab and click 'Refresh All' to update the Power Query table with your new data. Then, right-click your PivotTable and select 'Refresh' so it reflects the newly unpivoted data.

What is the difference between 'Unpivot Columns' and 'Unpivot Other Columns'?

'Unpivot Columns' transforms only the specific columns you have currently selected. 'Unpivot Other Columns' locks your currently selected columns in place and transforms all unselected columns. The latter is generally much safer for dynamic data where new value columns (like new months or accounts) might be added over time.

Excel & Data Problems