To group rows in Excel Power Query, open your data in the Power Query Editor, select Home > Group By, choose one or more columns to group on, then select an operation such as Sum or Count Rows and the column to summarize. Select OK to create a result with one row for each group; use Advanced in the dialog when you need multiple grouping columns or summaries.
What grouping rows means in Power Query
Grouping combines records that share the same value in one or more columns, then calculates a summary for each set. For example, a sales table with many transactions can become a shorter table showing total sales for each region. The original transaction rows are represented by their group and summary, rather than appearing individually in the result.
This is a data-transformation step, not just a way to hide rows on a worksheet. Power Query records the grouping in the query’s applied steps, so you can refresh the query later and apply the same transformation to updated source data. The grouped result can then be loaded to a worksheet or, where appropriate, the Data Model.
Grouping is useful for questions such as:
- What is the total sales amount for each product or region?
- How many orders came from each customer?
- What is the earliest or latest date recorded for each department?
- Which records belong to each category, if you need to inspect the underlying rows later?
How to group rows in Excel Power Query
These steps are for Excel for Microsoft 365 on Windows. Start with data arranged as a table with a single header row. If it is a normal cell range, select a cell in the data and choose Data > From Table/Range. Confirm the range and whether it has headers when prompted. Excel opens the Power Query Editor.
- In the editor, select Home > Group By.
- In the Group By dialog, choose the column that defines each group, such as Region.
- Choose a value for New column name, such as Total Sales.
- Choose the operation, such as Sum, and select the numeric column to summarize, such as Sales.
- Select OK and inspect the result. When it is ready, choose Home > Close & Load to return the result to Excel.
For example, suppose the source has Region, Product, and Sales columns, with several sales records for each region. Grouping by Region and summing Sales produces one row per region and a Total Sales column. If the East records are 120, 80, and 50, the grouped value is 250. The product details no longer appear as separate rows unless you also group by Product or retain the records in another way.
To group by more than one field, choose Advanced in the Group By dialog, then add grouping columns. Grouping by Region and Product produces one result for each unique region-product pair. The order of the grouping columns does not change which combinations are distinct, but it can affect how the fields are displayed in the result.
Choosing grouping columns and summary operations
Choose grouping columns that express the level of detail you want in the output. Group by Customer to get one row per customer; group by Customer and Month to get one row per customer-month combination. If you include a column with a unique value on every source row, such as an order ID, nearly every row may remain separate. That is a common reason a grouping step appears not to reduce the row count.
The operation should match the question and the data type. Common choices in the Group By dialog include:
- Sum: Adds numeric values, often used for sales, quantities, or costs.
- Count Rows: Counts the source records in each group. It does not require selecting a value column.
- Average, Median, Min, or Max: Summarizes a numeric or date column, as appropriate to the data.
- Count Distinct Rows: Counts distinct rows within each group. This is not the same as counting distinct values in one selected column.
- All Rows: Keeps the records for each group together as a nested table rather than reducing them to a single summary.
Available operations and whether they make sense depend on the column’s data type. For example, summing a column imported as text may not be offered or may not give the intended result. Check the type icon beside each column name and correct misclassified fields before grouping.
Using Advanced grouping for several calculations
Basic mode is suitable for one grouping field and one calculation. Choose Advanced when you want several grouping columns, several aggregations, or both. For example, group by Region and Product, then add Total Sales using Sum on Sales and Order Count using Count Rows. The output will have one row per region-product combination and separate columns for both calculations.
In Advanced mode, add each calculation separately and give its output column a useful name. Clear names such as Total Sales and Order Count make later steps, charts, and reports easier to understand than generic labels. You can also group by a date field, but decide first whether the source contains full timestamps. Two records on the same calendar day but at different times are different values, so they may form separate groups. If you want daily totals, create or transform a date-only field before grouping.
What All Rows does
All Rows is different from a summary such as Sum or Count Rows. It creates a column containing a nested table for each group. The outer result still has one row per group, but each nested table holds the source records that belong to that group. This is helpful when you want to keep group-level structure while doing further work on the records inside each group.
For instance, you could group by Customer using All Rows, then use each nested table to identify that customer’s latest transaction or perform another transformation. The nested values are not ordinary text that you can immediately read as a list of records in a worksheet. You may need to add a later Power Query step, expand the nested tables, or calculate a result from them. If all you need is a total or count, choose a direct aggregation instead.
Prepare data and check the result
Grouping is only as reliable as the values being grouped. Before applying it, review headers, data types, and consistency. Power Query treats values such as North, north, and North followed by a space as potentially different text values. Likewise, misspelled category names can create extra groups rather than being recognized as the same category.
- Use Transform > Format options, such as Trim or Clean, when text contains unwanted spaces or characters.
- Standardize inconsistent labels before grouping, or replace the incorrect values with a consistent category.
- Set amount fields to an appropriate numeric type and date fields to a date or date/time type.
- Decide how blanks and null values should be handled. Nulls in a grouping field can form their own group; filter or replace them first if that is not useful.
- Compare the sum or row count before and after grouping to check that the result is plausible.
Do not remove duplicates just to make the grouped result shorter unless duplicates are genuinely errors. Two identical-looking sales rows may represent two real transactions. Grouping with Count Rows can help reveal how many source records contribute to each result, but it does not decide whether those records should exist.
Common grouping problems and fixes
If the result has almost as many rows as the source, check whether the grouping keys include an ID, timestamp, or other field that varies on every record. Remove that field from the grouping keys if the desired output is at a broader level. If there are more groups than expected, inspect text for spaces, spelling variations, and inconsistent date or number types.
If a sum is missing, incorrect, or unavailable, check whether the source column was imported as text. Change its type to a numeric type and inspect any errors or non-numeric values before grouping. Currency symbols or unusual separators can prevent a clean conversion depending on the source’s regional format; use the appropriate locale when converting if necessary.
If a refreshed result does not reflect edits, remember that Power Query does not continuously update the loaded output as you type into the source. Refresh the query using Data > Refresh All, or right-click the output table and choose Refresh. A source formatted as an Excel table is generally easier to maintain as rows are added than a fixed cell range, but confirm that the query’s source includes the new data.
Power Query grouping compared with PivotTables
Both Power Query and PivotTables can summarize data by categories, but they serve different purposes. A PivotTable is often the quicker choice for interactive analysis: users can rearrange fields, filter, and change the displayed summary without building a transformation workflow. It leaves the underlying source data in place and is convenient for exploring different views.
Power Query is a better fit when the grouping is part of a repeatable cleanup or preparation process. Its steps can combine grouping with importing, filtering, changing types, and other transformations, then repeat those steps on refresh. The grouped output is a transformed table, not an interactive PivotTable. Choose based on whether you need a reusable data-preparation step or a flexible report to explore.
Refresh, load, and platform notes
After grouping, Home > Close & Load returns the query output to Excel using the available load options. If the source changes, refresh the query to rerun the grouping and update its output. A refresh may also fail if a source file moved, a column was renamed, or the query can no longer access the source, so keep source locations and column headers stable where possible.
The instructions above describe the Windows version of Microsoft 365. Power Query support and available connectors or editor features can vary by Excel edition and platform. On a Mac, check whether your installed Excel version provides the required Power Query Editor features; the ribbon and available options can differ. If Group By is available in the editor, the same general logic applies: select keys, choose an aggregation, and refresh the resulting query.
Frequently Asked Questions
Can I group rows by more than one column?
Yes. Open Home > Group By, choose Advanced, and add each grouping column. Power Query creates a group for each unique combination, such as each Region and Product pair.
How do I count rows in each group?
In the Group By dialog, select Count Rows as the operation. Choose the columns that define each group, but you do not need to select a column to count. The resulting value is the number of source records in that group.
Can Power Query group dates by month?
Yes, but group on a month-level field rather than a full timestamp if you want one result per month. Create a month or month-start date column before grouping; otherwise, different days or times may remain separate groups. Include the year as well when the data spans multiple years.
Does Group By delete rows from my source data?
No. Group By transforms the query result; it does not delete records from the original source table. The output contains grouped results, and you can edit or remove the grouping step in the query if you need a different view.
Why do identical-looking categories appear as separate groups?
The underlying values may differ because of leading or trailing spaces, capitalization, spelling, or data type. Clean and standardize the grouping column, then refresh or rerun the query. Also check whether the values only look identical because a date or number format hides a difference.
Related
- How to Set Up Transitions in PowerPoint
- PPE Inventory Tracking Spreadsheet: What to Track
- PowerPoint Slideshow Loop: Set It Up to Repeat Automatically
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Power Query Excel Number Format: What You Can ControlPower Query does not set Excel’s display number formats. It assigns data types—such as whole number, decimal number,…
- Excel Market Analysis: A Practical Guide to Market ResearchExcel market analysis means using spreadsheets to organize market data, estimate the size and value of customer segments,…
- Excel Data Model: What It Is and How to Use ItExcel’s Data Model is a built-in way to connect tables and analyze them together in PivotTables, without first…
- Delete a Page Break in Word: Manual and Automatic BreaksTo delete a manual page break in Word, show formatting marks, click immediately before the line labeled Page…
- Compare Two Excel Sheets: Find Differences Step by StepTo compare two Excel sheets, use a cell-by-cell formula when corresponding cells line up, or match records by…
- How to Use Slide Master View in PowerPoint EffectivelyTo use Slide Master view in PowerPoint for Microsoft 365 on Windows, choose View > Slide Master, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.