Table.Group
This function groups the rows of a table based on key columns specified by the user. The key can be a single column name or a list of column names. For each group, a record is created that includes th...
Table.Group(table as table, key as any, aggregatedColumns as list, optional groupKind as nullable number, optional comparer as nullable function) as tableThis function groups the rows of a table based on key columns specified by the user. The key can be a single column name or a list of column names. For each group, a record is created that includes the key columns and their corresponding values, as well as any aggregated columns specified by the user. Optionally, the user can also define groupKind and comparer.
If the data is already sorted by the key columns, specifying a groupKind of GroupKind.Local can improve the performance of grouping in certain situations. This is because rows with the same key values are assumed to be adjacent to each other.
When using a comparer, it is important to note that if it considers different keys to be equal, a row may be placed in a group with keys that are different from its own.
It is important to note that this function does not ensure the order of the rows it returns.
In Power Query, use the MTable.Group function to group a table by the "CustomerID" column. Add an aggregate column called "total" which calculates the sum of prices using the formula "each List.Sum([price])".
Example:
MTable.Group(
Table.FromRecords({
[CustomerID = 1, price = 20],
[CustomerID = 2, price = 10],
[CustomerID = 2, price = 20],
[CustomerID = 1, price = 10],
[CustomerID = 3, price = 20],
[CustomerID = 3, price = 5]
}),
"CustomerID",
{"total", each List.Sum([price])}
)
The output will be:
OutputTable.FromRecords(
{
[CustomerID = 1, total = 30],
[CustomerID = 2, total = 30],
[CustomerID = 3, total = 25]
},
{"CustomerID", "total"}
)