Table.Group

 

This topic applies to the Power Query Formula Language which can be used with Power Query and Power BI Desktop to build queries that mashup data. See the list of function categories.

Groups table rows by the values of key columns for each row.

Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as table  

ArgumentDescription
tableThe Table to modify.
keyThe key columns.
aggregatedColumnsSpecifies the names and function return types.
optional groupKindThe type of group. Possible values are GroupKind.Global (default) and GroupKind.Local.
optional comparerAn optional argument that determines equality between group keys.
  • The type of the resulting table is computed by preserving the columns that make up the group key, including their types, and appending new columns with names and types according to the names and function return types specified in the aggregatedColumns argument.

  • For each group, a record is constructed containing the key columns, including their values, along with any aggregated columns from the aggregatedColumns argument. A table of these group results is returned.

  • A group can be local (GroupKind.Local) or global (GroupKind.Global). A local group is formed from a consecutive sequence of rows from an input table with the same key value. A global group is formed from all rows in an input table with the same key value. Multiple local groups may be produced with the same key value but only a single global group is produced for a given key value.

  • The default groupKind value is GroupKind.Global.

  • The Table.Group function may also be used to nest the rows in a group.

let  
  
    Orders = Table.FromRecords({  
  
        [OrderID = 1, CustomerID = 1, Item = "Fishing rod", Price = 100.0],  
  
        [OrderID = 2, CustomerID = 1, Item = "1 lb. worms", Price = 5.0],  
  
        [OrderID = 3, CustomerID = 2, Item = "Fishing net", Price = 25.0],  
  
        [OrderID = 4, CustomerID = 3, Item = "Fish tazer", Price = 200.0],  
  
        [OrderID = 5, CustomerID = 3, Item = "Bandaids", Price = 2.0],  
  
        [OrderID = 6, CustomerID = 1, Item = "Tackle box", Price = 20.0],  
  
        [OrderID = 7, CustomerID = 5, Item = "Bait", Price = 3.25],  
  
        [OrderID = 8, CustomerID = 5, Item = "Fishing Rod", Price = 100.0],  
  
        [OrderID = 9, CustomerID = 6, Item = "Bait", Price = 3.25]  
  
    })  
  
in  
  
    Table.Group(Orders, "CustomerID", {"Total", each List.Sum([Price])})  

CustomerIDTotal
1125
225
3202
5103.25
63.25
Show: