Pivot Columns in Power Query: How to Reshape Data

To pivot columns in Power Query, select the column whose distinct values should become new headers, then choose Transform > Pivot Column. Pick the column containing the values to fill those headers and, if needed, choose an aggregation method for duplicate combinations.

What pivoting means in Power Query

Pivoting changes the shape of a table. Values that appear down one column become separate columns, while values from another column are placed into the resulting cells. It is useful when a source stores categories as rows but a report needs each category displayed across the page.

For example, a source might have three fields: Region, Quarter, and Sales. A row could read West, Q1, 1200. Pivoting the Quarter field creates headers such as Q1 and Q2, with the corresponding sales amounts in those columns. Region remains a row identifier. The result is a wider table with one row per region, assuming each region-quarter combination is unique or duplicates are aggregated.

Power Query determines which fields identify a row by using the columns that remain after you identify the pivot column and the values column. Those remaining fields act as grouping keys. That behavior matters: an extra field such as Salesperson can cause the result to contain separate rows for each salesperson instead of one row per region.

Prepare the source table

Before pivoting, check that the source has clear column headers and that each field contains the type of data it represents. In the example, Region and Quarter are text, and Sales is numeric. Remove blank rows or correct inconsistent category spellings such as Q1 and Quarter 1 if they are intended to mean the same thing. Power Query treats differently spelled values as different categories.

On Excel for Microsoft 365 for Windows, select a cell in the source range and choose Data > From Table/Range. If the data is not already an Excel table, confirm the range and whether it has headers in the dialog. This opens the Power Query Editor. If a query already exists, open it from Data > Queries & Connections by double-clicking its name.

Set the data types before pivoting when practical. Select a column and use the data type control on the editor ribbon, or use the type icon in the column header. Correct types help prevent problems such as attempting to sum text values. Keep only columns needed for the output: an unneeded column can become an unintended grouping key and create extra rows.

Pivot a column in the Power Query Editor

Select the column whose values should become headers. In the example, select Quarter, not Sales.
Choose Transform > Pivot Column. The Pivot Column dialog opens.
In Values Column, choose the field whose values should populate the new columns, such as Sales.
If there are duplicate combinations, open Advanced options and choose an aggregation method. For unique combinations, use the option that does not aggregate, where available.
Select OK and inspect the preview. Check that the new headers and row identifiers match what you intended.
Choose Home > Close & Load to load the result to Excel. Use the Close & Load To option if you need to choose a destination or connection-only loading.

The exact set of aggregation choices can depend on the data type and the Power Query interface. Common choices include Sum, Count, Minimum, Maximum, and Average. For sales totals, Sum is usually appropriate only when the repeated rows represent separate amounts that should be added, rather than accidental duplicates.

Worked example: quarterly sales by region

Suppose the source contains these rows: East, Q1, 800; East, Q2, 950; West, Q1, 1200; and West, Q2, 1100. Select Quarter as the pivot column and Sales as the values column. Because every Region and Quarter pair occurs once, the output can be:

East | Q1: 800 | Q2: 950
West | Q1: 1200 | Q2: 1100

This example also shows why column selection matters. If you select Sales as the pivot column, Power Query will try to create headers from the sales values, which is rarely the desired layout. If you leave an Order ID field in the query, each order may become its own row, so the output will not be a simple regional summary. Remove or retain fields according to the level of detail required.

New categories in later source data can also affect the shape of the result. If a refreshed source adds Q3, Power Query may add a Q3 column when the query’s pivot values are derived from the data. Review the refreshed output if downstream formulas, charts, or reports expect a fixed set of columns.

Duplicates and aggregation choices

A common reason a pivot fails is that the source has multiple rows for the same combination of identifiers and pivot value. For instance, two West-Q1 rows with sales of 500 and 700 cannot both occupy the single West/Q1 cell without a rule for combining them. With no aggregation, Power Query may report that it cannot convert a list of values into a single value.

Choose an aggregation that reflects the meaning of the data:

Sum adds numeric records, as when separate transactions should form a quarterly total.
Count counts records or values, depending on the selected option.
Minimum or Maximum returns the lowest or highest value in each group.
Average calculates a mean, which may not be meaningful if records have different weights or represent partial periods.

Do not select Sum simply to silence an error. First determine whether repeated rows are legitimate detail, accidental duplication, or evidence that another identifier is missing. If the underlying rows need deduplication, remove duplicates using the appropriate key before pivoting. If they are separate events, an aggregation may be right. A pivot changes layout; it does not decide what the data means.

What Power Query does behind the scenes

Power Query records each transformation as a step, so the pivot can run again when the query is refreshed. You can inspect or edit those steps in the Applied Steps pane. The generated M code commonly uses the Table.Pivot function. A simplified example for a table named Source is:

Table.Pivot(Source, List.Distinct(Source[Quarter]), "Quarter", "Sales", List.Sum)

This expression supplies the table, the distinct values to use as new columns, the attribute column, the values column, and an aggregation function. The final argument shown, List.Sum, sums duplicate values. When a pivot does not require aggregation, the generated expression can omit that argument. The exact code Power Query creates can differ based on the query and chosen options.

Understanding the step is useful when a source changes. For example, if the set of quarter values is hard-coded in a later step, new categories might not appear automatically. Inspect the formula bar or choose Home > Advanced Editor to review the query’s M code; change it only if you understand how the step affects the data.

Common problems and practical fixes

Unexpected extra rows: A field you did not mean to group by may still be in the query. Remove that field before pivoting, or accept that it defines a more detailed row level.
Null cells: A blank result usually means the source has no record for that row-and-category combination. It is not automatically the same as zero. Replace nulls with zero only when zero is correct for the analysis.
New columns are missing: Check whether the pivot step uses values from the data or a fixed list, and confirm the new category is spelled and typed consistently.
Values will not aggregate: Confirm the values column has a suitable type. Text-formatted numbers may need conversion to a numeric type, and text values cannot be summed meaningfully.
Headers look wrong: Check which field was selected as the pivot column and whether the source contains unexpected values, spaces, or inconsistent labels.

After loading, use Data > Refresh All to update the query from its source. Refreshing reruns the recorded steps; it does not automatically repair a transformation whose assumptions no longer fit the data. If a source column was renamed or removed, an affected step may need editing.

Pivoting versus unpivoting and PivotTables

Pivoting in Power Query is different from creating an Excel PivotTable. A Power Query pivot is a data-preparation step that reshapes records and can be loaded as a regular table. An Excel PivotTable is a reporting tool that summarizes data interactively, with fields that users can rearrange. They can complement one another: prepare or clean data in Power Query, then create a PivotTable from the result.

Unpivoting reverses the general direction. It turns multiple columns into attribute-value rows, which is often useful when a source has one column per month or product and needs to become a tidy list for analysis. In the Editor, select the columns to retain as identifiers and use Transform > Unpivot Other Columns, or select columns to convert and use Transform > Unpivot Columns. Choose pivoting when categories should become headers; choose unpivoting when headers encode repeated categories that belong in rows.

On Excel for Microsoft 365 for Windows, the steps above use the Windows Power Query Editor. Power Query availability, connectors, and interface details differ by Excel version and platform. Excel for Mac has Power Query capabilities, but not every Windows connector or feature is necessarily available in the same way; check the options in the installed Mac build before following a Windows-specific path. Excel for the web also has different query-editing capabilities from desktop Excel.

Frequently Asked Questions

Which column should I select when pivoting?
Select the column whose distinct values you want to turn into headers. Then choose a separate values column for the data to place under those headers.

Why does Power Query say there are duplicate values?
More than one source row maps to the same remaining identifier fields and pivot value. Choose a suitable aggregation, remove true duplicates, or include the missing identifier if those rows should remain distinct.

Can I pivot text values?
Yes, provided the result can be represented as a single value for each output cell. If duplicate combinations contain multiple text values, you need a suitable combining approach or must resolve the duplicates before pivoting.

Does Power Query pivoting update when I refresh?
Yes. Refresh runs the query steps again against the source. Whether new categories appear as columns depends on how the pivot step defines its pivot values and whether later steps expect a fixed set.

Should I use Power Query pivoting or an Excel PivotTable?
Use Power Query when you want a repeatable transformation that reshapes source data. Use a PivotTable when you want an interactive summary that users can rearrange. You can use both in the same reporting workflow.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.