Group rows in a table (Power Query)

Note: Power Query is known as Get & Transform in Excel 2016. Information provided here applies to both. To learn more, see Get & Transform in Excel 2016.

Note: For a very quick video on how to display Query Editor, see the end of this article.

In the Query Editor, you can group the values in various rows into a single value by grouping the rows according to the values in one or more columns. For more information, see the Combine data from multiple data sources tutorial.

Power Query has two types of Group By operations:

• aggregate a column with an aggregate function,

• or perform a row operation.

Group by one or more columns

Using the Query Editor ribbon : In the Query Editor ribbon, click Group By.

Using the Query Editor context menu : Right-click the column header that you want 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 popup window, 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 necessary. 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 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.

Aggregate a column using an aggregate function

In the Group By popup window, select Sum, Average, Min, or Max as the Operation.

Perform a Row Operation

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

 

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

 

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

 

Note: The Query Editor only appears when you load, edit, or create a new query using Power Query. The video shows that the Query Editor window appear after editing a query from an Excel workbook. To view the Query Editor without loading or editing an existing workbook query: From the Get External Data section in the Power Query ribbon tab, select From Other Sources > Blank Query.