Friday, January 7, 2022

Aggregate data from a column (Power Query) [MS]

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

Data Preview of the Orders table in the Northwind OData feed

The Power Query Query Editor

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 SourcesFrom 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.
     

    Previewing a related table in Navigator

    The Power Query Editor appears.

  5. In Data Preview, scroll to the right and click the expand icon (Expand) next to the Order_Details column. 

    Expand icon

    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.

    Aggregate 1

Result

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

Aggregate.2

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 Settings icon 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.

    Previewing a related table in Navigator

    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.

See Also

Power Query for Excel Help

Power Query M formula language reference (docs.com)

No comments:

Post a Comment

Search This Blog

Change or update your email password [MS]

Outlook Get Started Set up accounts ...