Power Query Excel: Aggregate Data into Summary Tables

How-to basics · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, Power Query aggregates data with Group By: load a table into Power Query, choose the column or columns that define each group, select an operation such as Sum or Count Rows, and load the resulting summary back to Excel. The source data stays intact, and you can refresh the query when the source changes.

What aggregation means in Power Query

Aggregation combines detailed rows into a smaller set of summary rows. For example, a sales table with one row per order line can become a table with one row per region, showing each region’s total sales. You decide what makes rows belong to the same group and what calculation to perform on the rows in each group.

This is different from simply sorting or filtering. Sorting changes the order of rows, and filtering hides rows that do not meet a condition. Group By calculates results for groups of rows. You can group by one field, such as Region, or by several fields, such as Region and Product. In the latter case, each distinct Region-and-Product combination gets its own summary row.

Power Query is a data preparation and transformation tool, not a worksheet formula. It records the steps you apply as a repeatable query. This makes it useful when you regularly receive a new export or append records and need to produce the same summary again.

Prepare the source data

Start with a rectangular dataset: one header row, one record per row, and one field per column. Avoid merged cells, blank header names, and decorative subtotal rows within the data. These features can make it harder for Power Query to identify the actual records and fields.

  • Give each column a clear, unique heading, such as OrderDate, Region, Product, and Sales.
  • Check that values in a column have a consistent meaning. A Sales column should contain numbers rather than a mixture of numbers and text such as “unknown.”
  • Use a real Excel date column for dates, and review the data types Power Query assigns when the query opens.
  • Convert the range to an Excel table if it is not already one. Select a cell in the range and use Insert > Table, then confirm that the table has headers.

A table is a convenient source because it can expand as you add rows, and the query can refer to its name. On Windows, select a cell in the table and choose Data > From Table/Range to open it in Power Query Editor. If you are importing a file or database instead, choose the appropriate command in Data > Get Data and select Transform Data when the import dialog offers that choice.

Group rows and choose an operation

In Power Query Editor, select Home > Group By. In the dialog, choose the field to group on. The default mode groups by one field; choose Advanced when you need multiple grouping fields or more than one aggregation. For each output calculation, enter a new column name, select the column to calculate, and choose an operation.

Common operations include Sum, Average, Median, Minimum, Maximum, Count Rows, Count Distinct Rows, and All Rows. The options shown can depend on the selected column and the Power Query interface. For example, Sum is appropriate for numeric sales values, while Count Rows counts records in each group. A count of rows is not the same as a count of distinct customer IDs: for the latter, use a distinct-count operation on the relevant field if it is available, or transform the data to make the distinct records explicit.

When the settings look right, select OK. Power Query replaces the detailed rows in the preview with grouped results. Review the preview, then select Home > Close & Load to return the result to Excel. If you want to choose where it goes or whether it is loaded as a table or connection, use Home > Close & Load > Close & Load To when that command is available.

Example: total sales by region and product

Imagine a table named SalesData with columns OrderDate, Region, Product, CustomerID, and Sales. To find sales totals for each region and product, group by both Region and Product and add a Sum aggregation on Sales. The output has one row for each combination that appears in the source, plus a new total column. If North sold three products and South sold two, the summary contains five region-product combinations, assuming none of those combinations is repeated as a separate group.

You can add further calculations in the Advanced Group By dialog. For example, add a Count Rows result named OrderLineCount alongside the Sum of Sales. This shows both the total value and how many source rows contributed to each group. If each source row is an order line, that count is a count of lines, not necessarily a count of distinct orders. The distinction matters when one order contains several products or lines.

To report by month, first create a consistent month field from OrderDate, then group by that field along with Region or Product as needed. Grouping directly by the full date would generally create a separate group for each date, not one for each month. A month label alone can also combine January from different years, so use a month-start date or include both year and month in the grouping fields when the report spans multiple years.

Choose grouping fields and calculations carefully

The fields you group on determine the level of detail in the answer. Group by Region for a regional total; add Product for a more detailed breakdown. Adding more grouping fields creates more combinations and usually more output rows. Removing a field combines records that previously appeared separately. Before loading the result, check whether the output’s level of detail matches the question you are trying to answer.

Choose an operation that makes sense for the measure. Sum is often useful for revenue or units, while Average may help describe a typical value. An average of transaction amounts is not the same as average revenue per customer if customers have different numbers of transactions. For that measure, you may need to aggregate to customer first and then calculate an average across customers. Counts also require care: Count Rows counts records, while counting unique people or orders requires distinct values or a suitable intermediate transformation.

Power Query’s aggregation is generally performed on the rows in each group after preceding query steps. If you filter out records before Group By, those records will not contribute to the totals. Review the steps listed in the Query Settings pane, since changing their order can change the result.

Power Query Group By compared with PivotTables

Both Power Query and PivotTables can summarize data, but they suit different tasks. A PivotTable is designed for interactive analysis in Excel: readers can rearrange fields, expand categories, and change the displayed summary. It is a good fit when people need to explore the data directly or switch between views.

Power Query is designed to shape and prepare data through a repeatable sequence of steps. A Group By query can produce a clean summary table that becomes an input to another query, a chart, or a PivotTable. It is especially useful when the same cleanup and aggregation must be repeated on refreshed source data. A common workflow is to use Power Query to combine, clean, and group records, then use a PivotTable to explore the prepared result.

Neither tool is automatically the better choice. Use a PivotTable for flexible, user-driven summaries; use Power Query when the transformation itself needs to be documented and repeated. You can also use both, rather than treating them as competing options.

Refresh the summary and maintain the query

After loading the query, changes to its source do not necessarily appear in the output immediately. In Excel, use Data > Refresh All to rerun queries and update connected results. If the source is an Excel table, adding rows within or immediately below that table can expand it; confirm the table includes the new records before refreshing. If the source is an external file, refresh also depends on the file remaining available at the expected location and the connection being accessible.

Power Query keeps a sequence of applied steps, such as selecting columns, changing data types, filtering, and grouping. You can reopen the query from Data > Queries & Connections by opening the query for editing, then adjust the steps in Power Query Editor. If a step refers to a column that was renamed or removed in the source, later steps can fail. Stable column names and a consistent source layout make refreshes more reliable.

For a useful handoff, name the query and output columns clearly. A heading such as TotalSalesByRegion is more informative than Sum of Sales when several summaries appear in the workbook. Keep a copy of the detailed source or retain a connection to it if users may need to audit how the totals were formed.

Common problems and practical checks

  • Numbers are treated as text: Sum may be unavailable or totals may be incomplete. In Power Query Editor, check the type icon beside the column heading and set the column to a numeric type using Transform > Data Type. Review errors or values that cannot be converted.
  • Unexpected extra groups appear: Values that look identical may differ because of spaces, spelling, punctuation, or inconsistent capitalization. Clean or standardize the grouping column before grouping, and inspect the distinct values.
  • Totals are too high or too low: Check whether the source contains duplicate rows, whether a filter removed records, and whether the selected operation matches the question. Also verify that each input row represents the unit you think it does.
  • Blank values form their own group: Null or blank grouping values can appear as a separate result. Decide whether those records should remain, be relabeled, or be filtered out before grouping.
  • Month totals mix years: Grouping on a month name alone can combine the same month across different years. Include the year, or group on a date field representing the first day of each month.

For a quick quality check, compare the sum of the grouped totals with the sum of the eligible source values when the operation is Sum and no records should have been excluded. A discrepancy is a prompt to review filters, errors, data types, and the selected source—not proof by itself that the query is wrong.

Availability on Mac and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows. Power Query is also available in some Microsoft 365 versions of Excel for Mac, but supported connectors and editing capabilities can vary by Excel build. The precise ribbon labels and import options may differ, so check the Data tab and the Power Query options available in the installed version. A workbook created on Windows may also rely on a connector that is unavailable on another platform.

Excel for the web has different Power Query capabilities from the Windows desktop application, and not every query can be created, edited, or refreshed there. If a workbook uses a connector or transformation that the web version does not support, use desktop Excel for the relevant work. Before relying on a cross-platform refresh process, confirm that the specific source connection and query steps are supported in each environment.

Frequently Asked Questions

Can Power Query group data and sum values?
Yes. Use Group By, choose the grouping column or columns, and select Sum for the numeric field. You can add other aggregations, such as Count Rows, in the same advanced grouping setup.

Does Group By change or delete my original data?
No. Group By changes the query’s output, not the source table or file. The grouped result is loaded separately, although you can choose where the query output is placed in the workbook.

Can I group by more than one column?
Yes. Open Group By, select Advanced, and add the grouping columns. Each distinct combination of their values becomes a group.

How do I count unique customers in each group?
Use a distinct-count aggregation on CustomerID if that operation is available in the Group By dialog. Check the result against a small sample, especially if CustomerID contains blanks or inconsistent values. Count Rows alone counts records, not unique customers.

Will the summary update when I add more source rows?
It updates when the query is refreshed, provided the new rows are included in the source. Use an Excel table or a suitably defined external source, then choose Data > Refresh All. Check that the source range or table actually includes the added records.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.