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.

Power Query enhances self-service business intelligence (BI) for Excel with an intuitive experience for discovering, combining, and refining data across a wide variety of sources—including relational, structured/semi-structured, OData, Web, Hadoop, and more.

Using the Query Editor, you can aggregate one or more columns of a linked table. For example, you can aggregate the sum of order details for each order.

Aggregate a column

Follow these steps:

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

   

2. In the Table link column drop-down:

   1. Click the Aggregate option.

   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.

   

This will add a new aggregate column to the table. The new column name will be of the form <AggregateFunctionName> of <ColumnName>.

For example, Sum of Order_Details.UnitPrice and Average of Order_Details.UnitPrice.

Quick Start Example

In this Quick Start example, you'll 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.

Follow these steps:

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.