How to Use Group By in Power Query in Power BI

In Power BI's Power Query, use the Group By feature to summarize data by one or more columns. You select the columns to group on and define aggregation operations like sum, count, or average on other columns. This creates a new summarized table based on your grouping and aggregation choices.

Syntax

The Group By operation in Power Query uses the following syntax pattern:

• Table.Group(table, keys, aggregatedColumns)

Where:

• table is the input table to group.
• keys is a list of column names to group by.
• aggregatedColumns is a list of records defining new columns with aggregation functions.

m

Table.Group(Source, {"Category"}, {{"Total Sales", each List.Sum([Sales]), type number}})

Example

This example groups sales data by the Category column and sums the Sales values for each category.

m

let
    Source = Table.FromRecords({
        [Category="Fruit", Sales=10],
        [Category="Vegetable", Sales=20],
        [Category="Fruit", Sales=15],
        [Category="Vegetable", Sales=5]
    }),
    Grouped = Table.Group(Source, {"Category"}, {{"Total Sales", each List.Sum([Sales]), type number}})
in
    Grouped

Output

Category | Total Sales
Fruit    | 25
Vegetable| 25

Category	Total Sales
Fruit	25
Vegetable	25

Common Pitfalls

Common mistakes when using Group By in Power Query include:

• Not specifying the aggregation function correctly, which causes errors.
• Grouping by columns that contain null or inconsistent data, leading to unexpected groups.
• Forgetting to set the correct data type for the aggregated column, which can cause issues in later steps.

Always check your aggregation function and data types after grouping.

m

/* Wrong: Missing aggregation function */
Table.Group(Source, {"Category"}, {{"Total Sales", each [Sales], type number}})

/* Right: Using List.Sum aggregation */
Table.Group(Source, {"Category"}, {{"Total Sales", each List.Sum([Sales]), type number}})

Quick Reference

Parameter	Description	Example
table	The input table to group	Source
keys	List of columns to group by	{"Category"}
aggregatedColumns	List of aggregation definitions	{{"Total Sales", each List.Sum([Sales]), type number}}
aggregation functions	Functions like List.Sum, List.Count, List.Average	List.Sum([Sales])

Key Takeaways

Use Table.Group to group data by one or more columns in Power Query.

Always specify an aggregation function like List.Sum or List.Count for grouped columns.

Check and set the correct data type for aggregated columns after grouping.

Avoid grouping on columns with null or inconsistent data to prevent unexpected results.