Table.AggregateTableColumn

Table

Expands a column of nested tables by applying aggregation functions to each nested table.

Syntax

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

Parameters

Name	Type	Required	Description
`table`	`table`	Yes	The table containing a column of nested tables.
`column`	`text`	Yes	The name of the column containing nested tables.
`aggregations`	`list`	Yes	A list of {newColumnName, aggregationFunction, newColumnType} triples defining how each nested table is aggregated.

Return Value

table — The original table with the nested table column replaced by aggregated result columns.

Remarks

Table.AggregateTableColumn is an alternative to Table.ExpandTableColumn when you want to aggregate (rather than flatten) the nested tables in a column. Each aggregation is defined as a three-element list: {newColumnName, aggregationFunction, newColumnType}.

The aggregationFunction receives the nested table as its argument and returns a scalar result. Common choices include Table.RowCount, List.Sum, List.Max, etc.

This is particularly useful after a Table.Group or Table.NestedJoin step when you want summary statistics instead of expanded rows.

Examples

Example 1: Count rows in each nested table

let
    Source = Table.Group(
        #table({"Dept", "Salary"}, {{"HR", 50000}, {"IT", 80000}, {"HR", 60000}}),
        "Dept",
        {{"Employees", each _, type table}}
    )
in
    Table.AggregateTableColumn(Source, "Employees", {{"Count", Table.RowCount, type number}})

Applied Steps

The final output — Table.AggregateTableColumn replaces the Employees column with a Count column holding Table.RowCount of each nested table: 2 for HR and 1 for IT.

	Dept	Count
1	HR	2
2	IT	1

Example 2: Sum a column in each nested table

let
    Grouped = Table.Group(
        #table({"Region", "Sales"}, {{"North", 100}, {"North", 200}, {"South", 150}}),
        "Region",
        {{"Data", each _, type table}}
    )
in
    Table.AggregateTableColumn(Grouped, "Data", {{"TotalSales", each List.Sum([Sales]), type number}})

Applied Steps

The final output — Table.AggregateTableColumn replaces the Data column with TotalSales, the sum of Sales in each nested table: 300 for North and 150 for South.

	Region	TotalSales
1	North	300
2	South	150

Compatibility

✓ Power BI Desktop✓ Power BI Service✓ Excel Desktop✓ Excel Online✓ Dataflows✓ Fabric Notebooks

Contributors