Excel Power Query Pivot Table: How to Build One

Charts & pivot tables · Updated November 21, 2026

Yes—Power Query can pivot a column of values into separate columns, and you can load the result into Excel and summarize it with a PivotTable. To pivot in Power Query, select the column whose distinct values should become headers, choose Transform > Pivot Column, select the column containing the values, and choose an aggregation if needed; to create an interactive PivotTable, load the query result and use Insert > PivotTable.

Power Query pivot and PivotTable are different tools

The word pivot refers to two related but distinct operations in Excel. Power Query’s Pivot Column command reshapes a table before it reaches a worksheet: values in one field become column headings. An Excel PivotTable summarizes data interactively, letting a reader rearrange fields, filter records, and change the grouping without editing the source query.

For example, a sales table might contain one row per transaction, with Region, Month, and Sales columns. Power Query can turn the Month values into columns such as January and February. A PivotTable can then summarize sales by region, month, salesperson, or another available field. The first operation changes the data’s shape; the second provides a flexible report.

These tools often complement each other. Use Power Query to import, clean, combine, and reshape records, then use a PivotTable to explore or present the prepared data. You can also use only one of them: a query can produce a useful flat table without a PivotTable, and a PivotTable can summarize data without Power Query.

When to use Power Query’s Pivot Column command

Power Query pivoting is useful when your source is in a long, record-based layout and another system or report needs a wide layout. A long table stores a category and its value in separate fields on each row. Pivoting makes each category a separate field, which can be convenient for cross-tab reports, exports, or comparisons.

Suppose the source contains these records: North, January, 120; North, February, 150; South, January, 90; and South, February, 110. Pivoting Month using Sales as the values produces one row per region and columns for January and February. If there is more than one record for a region and month, Power Query needs an aggregation rule, such as Sum, to combine those records.

That result is not automatically an interactive PivotTable. It is a query output table with ordinary rows and columns. To let users rearrange fields and summaries, create a separate Excel PivotTable from the loaded result.

Prepare the source data first

Good source structure prevents many pivot errors. Put one field name in each column, keep records in rows, and avoid merged cells, decorative title rows, and manually inserted subtotal rows. Convert a worksheet range into an Excel table with Insert > Table, or select a cell in an existing table, before importing it. A table normally expands when new records are added.

On Excel for Microsoft 365 on Windows, select a cell in the table and choose Data > From Table/Range. If the source is not already a table, Excel may offer to create one. Power Query Editor opens with a preview of the data. Check that the headers are correct and that fields have appropriate data types. For example, Month labels should be text or dates as intended, and Sales should be a numeric type if you plan to sum it.

  • Remove empty rows and repeated header rows that are part of a pasted report.
  • Check that category labels are consistent; North and north may be treated as different values depending on the data and transformation.
  • Decide what a blank value means. A blank category may produce a blank heading or require cleanup, while a blank amount can affect the result.
  • Keep an identifier field, such as Region or Product, for the rows that should remain after pivoting.

How to pivot a column in Power Query

In Power Query Editor, select the column whose distinct values should become new column headings. In the example, select Month. Choose Transform > Pivot Column. In the dialog, choose Sales as the Values Column. If each combination of the remaining row fields and Month has only one value, you may be able to select Don’t Aggregate. If combinations occur more than once, choose an aggregation such as Sum or Average in Advanced options.

  1. Load or connect to a structured source, then open it in Power Query Editor.
  2. Confirm the fields and data types, and apply any needed filtering or cleanup.
  3. Select the category field to turn into headings, then choose Transform > Pivot Column.
  4. Choose the values field and an aggregation where required, then select OK.
  5. Review the preview for missing categories, unexpected columns, or errors before loading.

After the pivot, fields other than the selected pivot and values fields determine which records are grouped into output rows. If the source has Region, Product, Month, and Sales, keeping both Region and Product can produce one row per region-product combination. Removing Product before pivoting instead produces one row per region, with Sales combined across products when an aggregation is chosen. This is an important choice: the fields retained in the query affect the level of detail.

Choose the right aggregation

Aggregation is necessary when multiple source records map to the same output cell. For instance, if North has two January transactions of 40 and 80, pivoting Month with Sales and choosing Sum gives North a January value of 120. Choosing Average gives 60. These are different answers to different questions, so the setting should reflect what each source row represents.

  • Sum: appropriate for additive measures such as sales amounts or units sold.
  • Count: useful when the question is how many records fall into each group, rather than their total value.
  • Average: useful for a mean measurement when each row should contribute to the average.
  • Minimum or Maximum: useful when the lowest or highest value in each group is the intended result.
  • Don’t Aggregate: suitable only when each output intersection has a single value. Duplicate intersections can cause an error rather than a meaningful summary.

Be careful with averages. An average of already averaged values is not necessarily the same as an average across all original records, particularly when groups have different numbers of records. If you need a weighted or otherwise specialized calculation, preserve the underlying detail or calculate the appropriate measure separately instead of assuming a simple average is equivalent.

Load the query and create an Excel PivotTable

When the Power Query result looks right, choose Home > Close & Load to load it to a worksheet. To choose where it goes, use Home > Close & Load > Close & Load To, then choose a destination such as a new worksheet. A worksheet table is a straightforward source for a PivotTable.

Select a cell in the loaded query table and choose Insert > PivotTable. In the dialog, confirm the table or range and choose where to place the PivotTable. In the PivotTable Fields pane, drag fields to Rows, Columns, Values, or Filters. For example, place Region in Rows, Month in Columns, and Sales in Values. If Excel summarizes a numeric field by Count when you expected Sum, check the source data type and the value-field settings; numbers stored as text can prevent the expected numeric summary.

You can load a query as a connection without putting its full result in a worksheet, and some workflows use the Data Model. Those choices can suit larger or more complex models, but the exact setup depends on the report and available Excel features. For a first query-based PivotTable, loading the result as a worksheet table makes the data path easier to inspect.

Refreshing the data and keeping reports reliable

A Power Query result is not a live mirror of its source. When source records change, refresh the query to rerun its import and transformation steps. Use Data > Refresh All to update workbook connections and reports, or refresh the query from its table or Queries & Connections pane. If a PivotTable still shows old figures, refresh that PivotTable as well; right-click inside it and choose Refresh.

Refresh behavior depends on the source. A query that reads a worksheet table can pick up added rows when the table expands. A query pointed at a fixed cell range may not include records typed outside that range. External sources can also require updated credentials, an available network connection, or access permissions. Check the query’s source and refresh settings if the preview does not reflect the records you expect.

Pivoting creates a column for each distinct value found in the pivot field. If a new month or category appears on a later refresh, Power Query may add a new output column. This can affect formulas, charts, or downstream steps that assume a fixed set or order of columns. Design dependent reports to tolerate expected new categories, and inspect the output after changes to the source categories.

Common problems and how to resolve them

  • Duplicate values cause an error: choose an aggregation in the Pivot Column dialog, or investigate why more than one record exists for an output intersection.
  • Unexpected extra columns appear: look for spelling differences, spaces, inconsistent labels, or new category values in the pivot field. Clean or standardize those values before pivoting.
  • Values are missing or show as null: check for absent category combinations and blanks in the source. A blank result can mean no matching record exists; it is not automatically the same as zero.
  • Totals are wrong: verify which fields remain in the query, the selected aggregation, and whether each source row represents a distinct amount that should be added.
  • PivotTable values do not update: refresh the query and then the PivotTable, and confirm the PivotTable source points to the current query output table.

One common misconception is that blank cells should always be replaced with zero. That may be appropriate when a missing record means no activity, but it may be misleading if the source is incomplete or the value is unknown. Decide what a blank means in the context of the records before replacing it.

Which method should you choose?

Choose Power Query pivoting when you need a repeatable data-preparation step that turns category values into fields every time the source is refreshed. It is especially useful when importing files, cleaning inconsistent source data, or creating a fixed cross-tab layout for another process. The transformation is recorded as a query step, so the same reshape can run again on updated data.

Choose an Excel PivotTable when users need to explore the data, change grouping fields, filter interactively, or compare different summaries. It is usually the more flexible reporting surface. Choose both when you want a repeatable transformation followed by an interactive summary. If the final requirement is only to group and total a long table, a PivotTable alone may be simpler; if another system requires separate category columns, Power Query’s Pivot Column command may be the more direct fit.

To reverse a wide layout into a record-based one, Power Query also has Unpivot Columns and related unpivot commands on the Transform tab. Unpivoting can be useful when a source report has one column per month but you want a tidy Month-and-Value structure for analysis. It is not the same as refreshing a PivotTable; it is another data-shaping operation in the query.

Frequently Asked Questions

Can Power Query create a PivotTable?
Power Query can reshape and load data, but its Pivot Column command does not itself create an interactive Excel PivotTable. Load the query result, then select it and choose Insert > PivotTable.

What is the difference between Pivot Column and PivotTable?
Pivot Column changes the layout of data during query preparation, turning distinct values into headings. A PivotTable summarizes data in a report that users can rearrange and filter.

Why does Power Query ask me to aggregate values?
More than one source row maps to at least one output cell. Select an aggregation that matches the question, such as Sum for transaction amounts, or correct the source if those duplicates are unintended.

Can I refresh a PivotTable made from a Power Query table?
Yes. Refresh the query so its output is current, then refresh the PivotTable if its summary has not updated. Data > Refresh All can refresh workbook connections and reports, but check the result when the query and PivotTable depend on one another.

Does Power Query work the same on Mac or in Excel for the web?
This guide uses Excel for Microsoft 365 on Windows. Power Query availability, connectors, and editing options can differ on Mac and in Excel for the web, so the ribbon layout or supported sources may not match. For full Windows-style authoring steps, use the Windows desktop app.

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.