Table.AggregateTableColumn

Table.AggregateTableColumn is a Power Query M function that aggregates tables in a specified column into multiple columns containing aggregate values for the tables. The function returns a new table with the added aggregate columns.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.AggregateTableColumn(
   table as table,
   column as text,
   aggregations as list,
) as table

Description

Table.AggregateTableColumn aggregates a column of tables into multiple columns in the containing table. It aggregates tables in table[column] into columns with aggregate values, specified by aggregations.

Examples

Aggregate table columns in [t] in the table {[t = {[a=1, b=2, c=3], [a=2,b=4,c=6]}, b = 2]} into the sum of [t.a], the min and max of [t.b], and the count of values in [t.a].

// Output: Table.FromRecords( {[#"sum of t.a" = 3, #"min of t.b" = 2, #"max of t.b" = 4, #"count of t.a" = 2, b = 2]} )
Table.AggregateTableColumn( 
    Table.FromRecords( 
        {
            [
                t = Table.FromRecords( {
                    [a = 1, b = 2, c = 3],
                    [a = 2, b = 4, c = 6]
                } ),
                b = 2
            ]
        },
        type table [t = table [a = number, b = number, c = number], b = number]
     ),
    "t",
    {
        {"a", List.Sum, "sum of t.a"},
        {"b", List.Min, "min of t.b"},
        {"b", List.Max, "max of t.b"},
        {"a", List.Count, "count of t.a"}
    }
 )

Related functions

Other functions related to Table.AggregateTableColumn are:

• Table.First
• Table.FirstN
• Table.FirstValue
• Table.Last
• Table.LastN
• Table.Max
• Table.MaxN
• Table.Min
• Table.MinN
• Table.SingleRow

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-aggregatetablecolumn