Aggregate data from a column (Power Query)

In this example, you aggregate the Order_Details column from the Northwind Orders table. The data is imported from the sample Northwind OData feed.

1. In Excel, select Home > New Source > Other Sources > From OData Feed.

2. In the OData Feed dialog box, enter the URL for the Northwind OData feed.

   http://services.odata.org/northwind/northwind.svc

3. Select OK.

4. In the Navigator pane, select the Orders table in the left pane, the data is previewed on the right,  and then select Transform Data.
    

   

   The Power Query Editor appears.

5. In Data Preview, scroll to the right and click the expand icon () next to the Order_Details column. 
   
   
   
   Table is an example of a structured column that you can expand to see more columns. For more information, see Work with a List, Record, or Table structured column.

6. In the Table column drop-down list:

   1. Select the Aggregate option.

   2. Hover over an aggregate function item, such as Aggregates of UnitPrice.

   3. In the aggregate function drop down list, select one or more aggregate functions. For example, Sum and Average.

7. Select OK.

   

Result

Power Query expands the Order_Details column, replacing the column with the Sum of UnitPrice and Average of UnitPrice.

When you expand a structured column, such as Table, you may wonder where it comes from. To find out, you can preview the related table in the external data source.

1. In the Query Settings pane, under Applied Steps, select Source, and then select the Edit Settings icon. The Data Source dialog box appears, which varies depending on the external data source. In this example, it is the OData Feed dialog box. Make note of that data source.

2. Select Home > Recent Sources, and then select the data source from the list that matches the one you made note of in step 1. The Navigator dialog box appears.

3. By default, the list of all tables from the data source displays in the left pane. Select the related table to preview the data in the right pane. The name of the structured column is the name of the related table. In this example, select Order_Details.
   
   

   By default, you can see and preview all tables. But you can select Display Options to turn on or off Only Selected Items and Enable Data Previews to customize the display to your liking.