Group rows in a table

You can group the values in a number of rows into a single value by grouping the rows based upon the values in one or more columns. For more information about how to group rows, see the Combine data from multiple data sources tutorial.

Power Query has two types of Group By operations: aggregate a column based on an Aggregate Function, or perform a Row Operation. You group rows in the Query Editor.

ShowLearn more about the Query Editor

You can access the Query Editor from the Navigator pane when connecting to a data source, or at any time from the Workbook Queries pane or the contextual Query ribbon tab for an existing query. To learn how to load the Query Editor, see Power Query Quick Start.

I want to


Group by one or more columns

Using the Query ribbon

  1. In the Query ribbon, click Group By.

Using the Query Editor context menu

  1. Right-click the column header to group on, and click Group By.

To Group by one or more columns

  1. In the Query Preview grid, select one or more columns. For example, Year and Order_Details.ProductID.
  2. In the Group By dialog box, the column name that you right-clicked in step 1 is by default selected in the Group By drop-down list. You can select another column name from the drop-down list, if required. If you want to group on multiple columns, click +, and then select a column name from the Group By drop-down list in the newly added row. To remove a grouping column, click against the record.
  3. In the New column name textbox, enter a group by name.
  4. In the Operation drop-down, select an Aggregate Function or Row Operation.
  5. To aggregate a column, select the column to perform the Aggregate Operation on from the Column drop-down. A Row Operation does not require a Column since data is grouped based on table rows. To add an aggregation column, click +, and then select a column name from the Column drop-down list in the newly added row. To remove an aggregation column, click against the record.
  6. Click OK.

Top of Page Top of Page

Aggregate a column using an Aggregate Function

In the Group By dialog box, select Sum, Average, Min, or Max as the Operation.

Group By Dialog Box for Aggregate Operations

Top of Page Top of Page

Perform a Row Operation

In the Group By dialog box, select All Rows or Count Rows as the Operation.

Group By dialog box for Row Operations

  • If you select All Rows for grouping, you can later expand the new grouped column to select the columns that you want to expand.

Group: All Rows

  • If you select Count Rows for grouping, new grouped column only displays the number of row values for each grouped row value.

Group: Count Rows

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Excel 2010