To group data in Excel Power Query, select a column in the Power Query Editor, choose Home > Group By, then set the grouping column and an operation such as Sum or Count Rows. Use Advanced in the dialog to group by multiple columns or add several calculations at once; the result is a summarized table that can be loaded to Excel and refreshed later.
What grouping does in Power Query
Grouping collects rows that share the same value in one or more columns and produces one output row for each group. You choose what the output should calculate, such as a total, average, minimum, maximum, or row count. It is useful when a source has transaction-level detail but you need a summary for reporting.
For example, a sales table might contain a date, region, product, and amount on every transaction. Grouping by Region and summing Amount creates one row per region with its total sales. Grouping by both Region and Product instead creates one row per region-product combination. The original transaction rows remain in the source; Power Query creates a transformed result rather than editing those source records in place.
Group By is different from simply sorting or filtering. Sorting changes row order, while filtering hides rows from the result. Grouping combines rows into categories and calculates or retains information for each category.
Prepare the source data
Power Query works most reliably when the source is a rectangular table with one header row, no merged cells, and a consistent type in each column. If the data is an ordinary range, select a cell in it and use Insert > Table, or press Ctrl+T on Windows. Check that My table has headers is selected when appropriate, then select a cell in the table and choose Data > From Table/Range.
In Excel for Microsoft 365 on Windows, this opens the Power Query Editor. Check that the headers are correct and that columns have sensible data types before grouping. A number stored as text may not be available for a numerical aggregation, and dates stored inconsistently can form unexpected categories. Select a column and use the data type control on the Home or Transform tab to correct its type if needed.
- Remove irrelevant rows and columns if they make the query harder to understand.
- Correct inconsistent labels, such as extra spaces or spelling variations, before grouping by text.
- Check that amounts are numeric and date fields are dates, not text.
- Decide whether blank values should remain as a group or be filtered out.
Cleaning first matters because Power Query groups the values it receives. For example, two labels that look almost identical may remain separate if one contains a trailing space. Grouping will not automatically determine that differently written product names refer to the same product.
Group by one column
To make a basic summary, select a column to use as the category, then choose Home > Group By. In the dialog, keep Basic selected. Choose the grouping column, enter a name for the new result column, select an operation, and select a column to aggregate when the chosen operation requires one. Click OK to add the grouping step to the query.
- Open the source in the Power Query Editor.
- Select Home > Group By.
- Choose the field to group by, such as Region.
- Give the output column a clear name, such as Total Sales.
- Choose Sum and select the numeric Amount column.
- Select OK and inspect the summarized rows.
For a row count, choose Count Rows. That operation counts records in each group and does not require choosing a numeric field. Rename the generated column to something meaningful, such as Transaction Count. Use descriptive names because they become headers in the result table and may be used by reports downstream.
Group by multiple columns or calculate several results
Choose Advanced in the Group By dialog when a summary needs more than one grouping field or more than one aggregation. To group by Region and Product, add both fields under the grouping section. Power Query then creates a separate output row for each distinct combination of those values, rather than one row for every region alone.
Advanced mode also lets you add additional aggregations. For instance, group by Region and add Sum of Amount and Count Rows. The output will contain the region, its total amount, and the number of source records in that region. You can add another aggregation, such as Maximum of Amount, if you also need to see the largest transaction in each group. Give each result column a distinct, informative name.
Think carefully about the grouping fields: each extra field makes groups more specific and usually increases the number of output rows. Grouping sales by Region may produce a short summary; adding Product, Salesperson, and Date could leave many small groups and may no longer answer a high-level reporting question.
Choose the right operation
The aggregation should match the question, not just the data type. Common options in the Group By dialog include Sum, Average, Median, Minimum, Maximum, Count Rows, and All Rows. The available operation and the fields you can select depend on the data and the operation chosen.
- Sum: totals a numeric field, such as revenue or units sold.
- Average or Median: summarizes a numeric field in different ways; the median is less affected by unusually high or low values.
- Minimum or Maximum: returns the lowest or highest value in a group, such as the earliest date or largest order.
- Count Rows: counts source records in each group, regardless of whether a particular amount field is filled in.
- All Rows: creates a nested table for each group instead of reducing the group to a single aggregate value.
Count Rows is not necessarily the same as counting nonblank values in one column. If some records have a blank customer ID, for example, Count Rows still counts those records. If the question is how many records have a populated ID, clean or filter the data deliberately, or choose a method that counts that field rather than assuming the two counts match.
Keep detail with All Rows
Most grouping operations reduce each group to a summary. All Rows is different: it keeps the records for each group together in a nested table shown in the result. This can be useful when the next step will inspect or transform each group, or when you need to retain access to the underlying records rather than just calculate totals.
A nested-table result is not the same as a flat summary table. It may need additional steps to expand or process the tables, and it can be less convenient for a simple worksheet report. If you need both a transaction-level table and a summary, keep the detail query and create a separate grouped query that references it. That way, the summary does not replace the detailed result in your reporting workflow.
Load and refresh the grouped result
After checking the result in the editor, choose Home > Close & Load to return it to Excel using the default load behavior. Where available, the Close & Load To option lets you choose how and where to load the query, such as a worksheet table or a connection. A worksheet table is usually easiest to inspect and use in formulas or charts.
When the source data changes, refresh the query instead of rebuilding the summary. In Excel for Windows, use Data > Refresh All to refresh workbook connections, or right-click the loaded query table and choose Refresh. Power Query reruns its saved steps against the source. If new data is added outside the original source range, confirm that the source itself includes those new rows; a query cannot summarize records it does not receive.
Excel for Mac also includes Power Query capabilities in supported Microsoft 365 releases, but the available connectors and some interface details can differ from Windows. Excel for the web has Power Query support with capabilities that depend on the workbook and available features; editing options are not identical to the Windows desktop editor. If a command or connector is missing, use the desktop version that supports the needed query rather than assuming the grouping steps are broken.
Common problems and how to fix them
- Totals are missing or wrong: Check the data type of the field being summed. Text values that resemble numbers may need to be converted to a numeric type. Also inspect the source for errors and unexpected blank values.
- There are more groups than expected: Look for inconsistent spelling, leading or trailing spaces, different date-time values, or extra grouping fields. Clean the relevant column or use a date-only value when the time of day should not distinguish groups.
- A count seems too high: Confirm whether you chose Count Rows or another count operation, and check whether duplicate records are present. Grouping does not automatically remove duplicates from the source.
- New records do not appear after refresh: Check that the query source includes them. An Excel Table normally expands as rows are added directly below it, while a fixed range may not.
- The output order changes: Grouping defines categories and aggregates, not a permanent display order. Add a sort step after grouping if the report needs a particular order.
Power Query records transformation steps, so you can select a step in the Applied Steps pane to review the result at that point. If a correction changes the source structure, such as a renamed header, later steps that refer to the old name may need to be updated.
Power Query grouping compared with PivotTables
Power Query and PivotTables both summarize data, but they serve different roles. Power Query is a data preparation and transformation tool: it can import, clean, combine, and reshape source data, then save those actions as repeatable steps. A PivotTable is primarily an interactive reporting tool for rearranging fields and exploring a summary after the data is available.
Choose Power Query when the same cleanup and grouping needs to be repeated on refreshed files, or when the grouped output is itself a useful clean table for another workbook task. Choose a PivotTable when you want to drag fields between rows, columns, and values to explore different views quickly. They can also be used together: prepare data with Power Query, then build a PivotTable from the loaded result or from an appropriate data model.
For a one-off total from a small, stable range, a worksheet formula or a PivotTable may be quicker. For a repeatable process involving imported files, inconsistent source layouts, or multiple transformation steps, Power Query can make the workflow easier to refresh and audit.
Frequently Asked Questions
Can I group by more than one column in Excel Power Query?
Yes. Open Home > Group By, choose Advanced, and add each field that defines a group. Power Query returns one row for each distinct combination of the selected fields.
Can I add a sum and a row count in the same grouping?
Yes. In Advanced mode, add multiple aggregations, such as Sum of Amount and Count Rows. Give each output column a clear name so it is easy to interpret after loading.
Does Group By change my original Excel data?
No. It adds a transformation to the query and creates a grouped result. The source data remains separate, although loading a result to a worksheet location that already contains information can affect that destination, so choose the load location carefully.
How do I group dates by month instead of by exact date?
Create a month-level field before grouping, then group by that field. For example, use the date transformations available on the Transform tab to derive an appropriate date part or create a month-start date. Grouping directly by a date-time column may treat different days or times as separate groups.
Can I undo a grouping step?
In the Power Query Editor, remove or edit the Grouped Rows step in Applied Steps. This changes the query definition, not the source records. You can also close the editor without applying changes if you decide not to keep the edits.
Related
- How to Record a PowerPoint Presentation in Zoom
- Parts Inventory Spreadsheet: What to Track and How
- Online Expense Tracking Spreadsheet: How to Set It Up
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Resize an Image in an Excel Cell and Keep It FittedTo resize an image so it fits an Excel cell, insert it with Insert > Pictures > Place…
- Open Excel Files in Google Sheets: A Step-by-Step GuideTo open an Excel workbook in Google Sheets, upload the .xlsx or .xls file to Google Drive, then…
- Yes/No Dropdown in Google Sheets: Add One in MinutesTo add a Yes/No dropdown in Google Sheets, select the cell or cells, choose Insert > Dropdown, enter…
- Merge Cells in Google Sheets: Formula vs. Menu OptionNo Google Sheets formula can merge cells in the spreadsheet layout. To combine the contents of cells in…
- How to Consolidate Data in Excel: Step-by-Step GuideTo consolidate data in Excel for Microsoft 365 on Windows, select the destination cell, choose Data > Consolidate,…
- How to Lock Cells in Excel and Protect a WorksheetTo lock cells in Excel, select the cells, open Format Cells, check Locked, then go to Review >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.