Aggregate data from a column (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.

With Power Query, you can aggregate the columns of a linked table. For example, you can aggregate the sum of order details for each order. You'll use Query Editor.

Aggregate a column

1. In Query Editor, select the expand icon ( ) in a column header containing a related Table link.

   

2. In a Table link column drop-down:

   1. Click Aggregate.

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

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

3. Click OK.

   

The table now contains a new aggregate column. The new column name is in the format <AggregateFunctionName> of <ColumnName>. For example, Sum of Order_Details.UnitPrice and Average of Order_Details.UnitPrice.

Quick Start

In this Quick Start, you aggregate the Order_Details column from the Northwind Orders table.

Note:  This Quick Start uses the Northwind Open Data Protocol (OData) feed. An OData feed is a uniform way to expose data on the web. Similar to a web page, you access an OData feed with a url.

1. In the POWER QUERY ribbon tab, click From 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. Click OK.

4. In the Navigator pane, double click the Orders table or click Orders and click Edit Query.

5. In the Query Editor dialog box, scroll to the right and click the expand icon ( ) next to the Order_Details column.

6. In the expand column drop-down:

   1. Click Aggregate.

   2. Hover over Sum of UnitPrice and click the aggregate function drop down.

   3. Select Sum and Average. The Sum of UnitPrice label changes to Aggregates of UnitPrice.

7. Click OK.

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