Aggregate data from a column

With Microsoft Power Query for Excel, you can aggregate the columns of a linked table. For example, you can aggregate the sum of order details for each order.

Aggregate a column

  1. Click the expand icon (Expand column icon ) in a column header containing a related Table link. For example, an Order table has a related Order_Details 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.

Aggregate 1

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.

Aggregate.2

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.
  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 (Expand ) 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.
  1. Click OK.

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

 
 
Applies to:
Excel 2013, Excel 2010