Excel Power Query Pivot Column: Steps and Examples

Charts & pivot tables · Updated November 21, 2026

To pivot a column in Excel 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 new columns, and choose an aggregation if multiple source rows share the same row-and-column combination.

What Pivot Column Means in Power Query

Pivot Column reshapes a table from a long layout into a wider one. Values found in one selected column become separate column headers, while another column supplies the values placed under those headers. Remaining columns identify the rows in the result. Power Query performs the reshaping as a recorded transformation, so you can refresh the query when the source data changes.

For example, a sales list might contain one row per region and month, with columns named Region, Month, and Sales. Pivoting Month produces columns such as Jan, Feb, and Mar; Sales supplies the numbers in those columns; and Region remains as the row identifier. The result is often easier to read as a report, compare periods across one row, or pass to another analysis step.

This is a data transformation, not just a visual formatting change. The query changes the table’s shape, and the resulting headers depend on the values present in the pivoted column. If new month names appear in the source and the query refreshes, Power Query can create corresponding columns.

Example: Turn Month Values into Headers

Suppose the source table contains these records: North, Jan, 120; North, Feb, 150; South, Jan, 90; and South, Feb, 110. Pivoting Month using Sales as the values column gives a table like this:

  • North: Jan 120, Feb 150
  • South: Jan 90, Feb 110

Region is retained as the identifier because it is the remaining descriptive column. The month labels become headers, and each intersection of a region and month receives the related sales figure. A source row for South in March would add a March value for South; depending on the source and refresh, March would also appear as a result column.

It helps to decide what each output row should represent before transforming the data. In this example, one output row represents one region. If the source also contains Product, and Product remains unpivoted, the result will usually have a row for each Region-and-Product combination. Every column you leave outside the pivot and values columns can contribute to defining distinct output rows.

How to Pivot a Column in Excel

These steps are for Excel for Microsoft 365 on Windows. Start with data that has clear headers and a consistent structure. If the data is an Excel table, click inside it and use Data > From Table/Range to open the Power Query Editor. For data from another source, use the appropriate command in Data > Get Data and open its query for editing.

  1. In Power Query Editor, identify the column whose entries should become headers, such as Month, Category, or Status.
  2. Select that column, then choose Transform > Pivot Column. The selected column provides the values that will become column names.
  3. In the dialog, choose the Values Column, such as Sales or Quantity. This is the source of the cells beneath the new headers.
  4. Open Advanced options if needed and choose how Power Query should combine repeated values. If every row-and-column combination is unique, you may not need an aggregation.
  5. Select OK and review the preview. Check the headers, the row identifiers, and any errors or null values before loading.
  6. Choose Home > Close & Load, or use Home > Close & Load To to select a destination or connection option.

The exact available choices in the dialog can depend on the data and Power Query interface. If your data has duplicates, select an aggregation that matches what the numbers mean rather than choosing one merely to make the error disappear. In Excel for Mac, Power Query capabilities and interface details can vary by version and source; use the Pivot Column command in the editor if it is available. Excel for the web does not provide the full Windows Power Query authoring experience.

Choosing an Aggregation

Power Query needs a rule when multiple source values would land in the same output cell. For example, if North has two January sales records, both would map to the North–Jan intersection. Depending on the selected aggregation, Power Query can add those records, average them, count them, or choose a minimum or maximum. The dialog’s available aggregation options may include sum, average, count, distinct count, minimum, maximum, and median.

  • Sum: Use for additive measures such as sales dollars or units sold when the records should be totaled.
  • Count: Use when the number of records is meaningful, rather than their numeric total.
  • Average: Use when the mean of repeated measurements is the intended value.
  • Minimum or maximum: Use when the lowest or highest recorded value is the meaningful result.

Consider two North-January sales entries, 50 and 70. Sum produces 120; average produces 60; count produces 2. These are all valid calculations, but they answer different questions. A sum is usually appropriate for transaction amounts, while taking the average of transaction amounts may be appropriate only if the goal is the average transaction size. Confirm the meaning of the source field before selecting the operation.

Duplicates, Missing Combinations, and Nulls

A common cause of a pivot error is assuming the chosen row identifiers and pivoted value form a unique pair when they do not. If two records have the same Region and Month, Power Query has more than one value for the same output cell. Choose a suitable aggregation, or investigate whether another field—such as Product, Store, or Transaction Type—should remain as an identifier to distinguish the records.

Do not add extra identifiers automatically. Keeping Product in the result changes the meaning of a row from “one region” to “one region and product,” which may be what you want, but it also makes the output more detailed. Conversely, removing a meaningful identifier can combine records that should stay separate. Check a few source rows against the resulting table to verify the intended grain.

When a combination is absent from the source, the corresponding result cell is generally null rather than zero. A null means no value was supplied for that combination; zero is an actual numeric value. Replacing null with zero can be useful for a report, but only if absence truly should be treated as zero. For example, a missing sales record might mean no sales, or it might mean the data was not collected. Preserve that distinction when it matters.

Also inspect the pivoted column for inconsistent labels. Values such as Jan, January, and a blank entry can create separate headers or an unexpected column. Standardize category values before pivoting when they are intended to represent the same thing.

Pivot Column Compared with a PivotTable and Unpivot

Power Query’s Pivot Column and Excel’s PivotTable both rearrange data, but they serve different purposes. A Power Query pivot is a step in preparing or shaping a query’s table. It can be repeated during refresh and is useful when the output itself needs a wide structure for another worksheet, model, or process. A PivotTable is an interactive summary report created from data, with controls for arranging fields and exploring totals.

Use a PivotTable when you mainly want to explore or summarize data interactively in Excel. Use Power Query Pivot Column when you need the query’s output table to have categories as columns, especially as one stage in a repeatable import-and-clean process. They can also be used together: Power Query can clean and reshape the source, and a PivotTable can summarize the loaded result.

Unpivot is the complementary transformation. It turns multiple category columns into rows, often producing attribute-and-value columns. If a source has Jan, Feb, and Mar as separate fields, unpivoting them can create one Month column and one Sales column. That long format is often preferable for analysis and combining data; pivoting is useful when a wide report layout is required. Choose the shape that suits the next task, not just the way the source currently looks.

Refreshing and Managing the Result

After loading a query, use Data > Refresh All or right-click the query output and choose Refresh to rerun it against its source. Refresh applies the saved transformation steps again; it does not mean that the pivot is recalculated as a worksheet formula. If the source gains a new category, the refreshed result may gain a new column. That can be helpful for a changing set of categories, but it also means downstream formulas or reports that expect a fixed set of headers may need attention.

Keep the source and output areas separate when loading a query to a worksheet. Avoid manually typing into cells that Power Query controls, because a later refresh can replace the output. If a downstream calculation needs stable column names, consider whether the source categories are controlled, or build later query steps that handle the set of columns appropriately.

When a query is intended only to feed another query or the Data Model, use Close & Load To to choose the relevant loading option rather than placing every intermediate table on a worksheet. The best destination depends on whether people need to inspect the transformed rows, use them in a PivotTable, or use them only as an intermediate step.

Common Problems and Ways to Diagnose Them

  • There is an error for repeated values: Check for duplicate combinations of the remaining identifier columns and the pivot column. Decide whether to aggregate or retain another identifier.
  • Unexpected headers appear: Check for inconsistent spelling, extra spaces, blank categories, or differently formatted values in the pivot column.
  • Values look too large: Review whether Sum is combining records that should be separated by another field, or whether the source already contains subtotals.
  • Some cells are blank: Look for missing combinations in the source. Decide whether null should remain distinct from zero before replacing it.
  • A new column appears after refresh: The source may contain a new category. Check whether dependent formulas, charts, or reports expect a fixed set of columns.
  • The result has too many rows: Review which non-pivot columns remain. Each distinct combination of those columns can define a separate output row.

For a reliable check, compare a small sample of source records with their output intersections and confirm the totals. If a total differs, investigate the source grain, aggregation, filters, and any rows removed earlier in the query. Fixing the cause in the relevant query step is usually more dependable than manually editing the loaded result.

Frequently Asked Questions

Which column should I select before choosing Pivot Column?
Select the column whose distinct entries should become the output headers, such as Month or Department. Then choose a separate Values Column containing the data to place beneath those headers.

Why does Power Query say there is more than one value for a cell?
More than one source row maps to the same combination of row identifiers and pivot value. Choose an appropriate aggregation, or keep an additional field as an identifier if those records should remain separate.

Can Power Query add new pivot columns when I refresh?
Yes. If a refreshed source includes a new value in the pivoted column, the result can include a corresponding new column. Check any formulas or reports that rely on a fixed set of headers.

Is Power Query Pivot Column the same as an Excel PivotTable?
No. Pivot Column reshapes a query’s output during data preparation. A PivotTable is an interactive Excel report for summarizing and exploring data. Choose based on whether you need a transformed table or an interactive summary.

How do I turn pivoted columns back into rows?
Use an unpivot transformation in Power Query. Select the columns to convert and choose Transform > Unpivot Columns, or select identifier columns and use Transform > Unpivot Other Columns, depending on the structure you need.

Related

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.