Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2021 Excel 2019 Excel 2016

In Power Query, you can group the same values in one or more columns into a single grouped row. You can group a column by using an aggregate function or group by a row. 

Example

The following procedures are based on this query data example:

Example of data before aggregation

You can group data by using an aggregate function, such as Sum and Average. For example, you want to summarize the total units sold at the country and sales channel level, grouped by the Country and Sales Channel columns.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, edit, and load a query in Excel.

  2. Select Home > Group by.

  3. In the Group by dialog box, select Advanced to select more than one column to group by.

  4. To add another column, select Add GroupingTip    To delete or move a grouping, select More (…) next to the grouping name box.

  5. Select the Country and Sales Channel columns.

  6. In the next section:New column name    Enter "Total units" for the new column header.Operation    Select Sum. The aggregations available are Sum, Average, MedianMin, MaxCount Rows, and Count Distinct Rows.Column     Select Units to specify which column to aggregate.

  7. Select OK.

Result

The results of grouping by aggregation

A Row Operation does not require a column, because data is grouped by a row in the Group By dialog box. There are two choices when you create a new column:

Count Rows     which displays the number of rows in each grouped row.Group: Count Rows

All Rows An inner Table value is inserted. It contains all the rows for the columns you grouped by. You can later expand the columns if you want. For more information, see Work with a List, Record, or Table structured column.Group: All Rows

Procedure

For example, to group by all rows, you want the total units sold and you want two other columns that give you the name and units sold for the top-performing product, summarized at the country and sales channel level.

  1. To open a query, locate one previously loaded from the Power Query Editor, select a cell in the data, and then select Query > Edit. For more information see Create, load, or edit a query in Excel.

  2. Select Home > Group by.

  3. In the Group by dialog box, select Advanced to select more than one column to group by.

  4. Add a column to aggregate by selecting Add aggregation at the bottom of the dialog box. Tip    To delete or move an aggregation, select More (…) next to the column box.

  5. Under Group by, select the Country and Sales Channel columns.

  6. Create two new columns by doing the following:Aggregation    Aggregate the Units column by using the Sum operation. Name this column Total units.All rows    Add a new Products column by using the All rows operation. Name this column Total products. Because this operation acts upon all rows, you don’t need to make a selection under Column and so it is not available.

  7. Select OK.

Result

Sample data grouping procedures

See Also

Power Query for Excel Help

Grouping or summarizing rows (docs.com)

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.