Summarize function

Applies to: Power Pages

Summarize records of a table by grouping on one or more columns.

Description

Use the Summarize function to group records of a table and summarize the columns in the group.

The first argument to Summarize is the table to operate on. The remaining arguments can be in any order and fall into two categories:

• Group column names. Use these to specify which columns to group on.
• Summarization formulas. Use these to summarize the columns in the ThisGroup table. ThisGroup is a table which contains all the columns of the original table, but is limited to just the records of one group at a time based on the group columns. Each formula must be named with As for column name in the result table.

A table is a value in Power Apps, just like a string or a number. You can specify a table as an argument for a function, and a function can return a table. Summarize doesn't modify a table; instead it takes a table as an argument and returns a different table. For more information, see working with tables.

Delegation

Summarize can be delegated depending on the data source and complexity of the summarization formulas. Basic aggregate functions such as Sum, Average, Max, Min, CountRows, and Concat have a good chance of being delegated.

If complete delegation of a formula isn't possible, the authoring environment flags the portion that can't be delegated with a warning. When possible, consider changing the formula to avoid functions and operators that can't be delegated.

For more information, see delegation overview.

Syntax

Summarize( Table, GroupByColumnName1 [, GroupByColumnName2, ... ] [, SummarizeColumns As SummarizeNames, ...] )

• Table - Required. Table to be summarized.
• GroupByColumnNames - At least one is required. The column names in Table by which to group records. These columns become columns in the resulting table.
• SummarizeColumns - Optional. Summarization formula over the ThisGroup table for each group.
• SummarizeNames - Required for each SummarizeColumn. Each summarized column must be explicitly named for the output table.

Examples

Simple example

1. Create a table in your Power Fx host with this sample data:

Set( CityPopulations,
   Table(
        { City: "London",    Country: "United Kingdom", Population: 8615000},
        { City: "Berlin",    Country: "Germany",        Population: 3562000},
        { City: "Madrid",    Country: "Spain",          Population: 3165000},
        { City: "Rome",      Country: "Italy",          Population: 2874000},
        { City: "Paris",     Country: "France",         Population: 2273000},
        { City: "Hamburg",   Country: "Germany",        Population: 1760000},
        { City: "Barcelona", Country: "Spain",          Population: 1602000},
        { City: "Munich",    Country: "Germany",        Population: 1494000},
        { City: "Milan",     Country: "Italy",          Population: 1344000}
    )
)

2. Evaluate the following formula:

Summarize( CityPopulations, Country,
           Sum( ThisGroup, Population ) As 'Total Population',
           Concat( ThisGroup, City, ", " ) As Cities 
)

The result is this table:

Multiple group columns

1. Create a table in your Power Fx host with this sample data:

Set( Inventory, 
   Table(
      {Supplier:"Contoso",  Fruit:"Grapes",  Price:220, Purchase:Date(2015,10,1), Tags: ["Red","Seedless"]},
      {Supplier:"Fabrikam", Fruit:"Lemons",  Price:31,  Purchase:Date(2015,10,1), Tags: ["Colombia"]},
      {Supplier:"Contoso",  Fruit:"Lemons",  Price:29,  Purchase:Date(2015,10,2), Tags: ["Peru"]},
      {Supplier:"Contoso",  Fruit:"Grapes",  Price:210, Purchase:Date(2015,10,2), Tags: ["Green","Seedless"]},
      {Supplier:"Fabrikam", Fruit:"Lemons",  Price:30,  Purchase:Date(2015,10,3), Tags: ["Mexico","Seedless"]},
      {Supplier:"Contoso",  Fruit:"Bananas", Price:12,  Purchase:Date(2015,10,3), Tags: ["Mexico"]}
   )
)

2. Evaluate the following formula:

Summarize( Inventory, Supplier, Fruit, Average( ThisGroup, Price ) As 'Average Price' )