Excel 2019 Bible

Chapter 39: Transforming Data with Power Query

39

Power Query offers a Group By feature that enables you easily group to data and create

aggregate views.

While in the Query Editor, select the Group By command on the Transform tab. The Group

By dialog box illustrated in Figure 39.30 will open.

FIGURE 39.30

The Group By dialog configured to sum potential revenue by State and City

You have the option of creating a Basic grouping with only one column to group by or creat-

ing an Advanced aggregation that uses multiple group by columns.

Let’s take a moment to walk through the steps for transforming data into an aggregate view

by City and State:

1. While in the Query Editor, activate the Group By dialog box (Transform ➪
   Group By).


2. Click the option for Advanced grouping. This exposes the fields that we need to
   group by multiple columns.


3. Use the Group by drop-down list to select the column on which you want to
   aggregate. Then click the Add Grouping button to add columns to the grouping.
   (In Figure 39.30, State and City were selected.)


4. Use the New column name input box to give the new aggregate column a name.