Power Query’s Group By feature transforms raw transaction-level data into meaningful summaries by consolidating rows based on shared characteristics. Whether you need to sum sales by product, count transactions by customer, or calculate average prices by region, the Group By function enables you to reduce massive datasets to their optimal granularity level with just a few clicks.

This comprehensive guide covers all grouping methods, from basic summaries to advanced multi-column aggregations with custom calculations.

Why Group Data in Power Query?

Data grouping is essential for professional analytics:

• Data reduction: Consolidate millions of rows into meaningful summaries
• Analysis efficiency: Work with aggregated data rather than transaction-level details
• Performance improvement: Smaller datasets load and calculate faster
• Business insights: Extract patterns and trends from raw data
• Consistency: Apply the same aggregation logic automatically with refreshes
• Flexibility: Multiple aggregation methods for different analytical needs

1. Understanding Power Query Group By

What Does Group By Do?

Group By accomplishes three key tasks:

1. Identifies unique values: Finds all distinct values in your grouping column(s)
2. Consolidates rows: Combines all rows with matching grouping values
3. Applies aggregation: Calculates a new value using functions like Sum, Average, Count, etc.

When to Use Group By

• Summarizing sales transactions by product or region
• Calculating customer lifetime value across purchase history
• Counting distinct transactions by date
• Finding average prices or metrics by category
• Preparing data for pivot tables or analysis

2. Basic Group By (Single Column)

Method: Using the Basic Group By Dialog

1. In Excel, go to Data > Edit Query (or Data > Get Data > Edit Query)
2. In the Power Query Editor, click the Transform or Home tab
3. Select Group By from the ribbon
4. The Group By dialog opens with Basic selected by default
5. Configure the following:
   • Column to group by: Select the column containing values to group (e.g., Product)
   • New column name: Name for your summary column (e.g., “Total Sales”)
   • Operation: Choose the aggregation function (Sum, Average, Count, etc.)
   • Column: Select the column to aggregate (e.g., Sales Amount)
6. Click OK to apply the transformation

Example: Summarize Sales by Product

Setting	Value
Column to group by	Product
New column name	Total Sales
Operation	Sum
Column	Amount

Result:

Original data with 1,000 transaction rows becomes a summary with one row per product showing total sales.

3. Advanced Group By (Multiple Columns)

Method: Using Advanced Group By

1. Click Group By from the ribbon (or Transform > Group By)
2. Click the Advanced button to enable multi-column grouping
3. Select columns to group by (you can add multiple):
   • Click on first grouping column
   • Click Add Grouping to add additional columns
   • Use the three dots (…) menu to remove or reorder columns
4. In the New column name section, define aggregations:
   • Enter the new column name
   • Select the Operation (Sum, Average, Count, etc.)
   • Select the Column to aggregate
5. Click Add aggregation to add multiple summary calculations
6. Click OK to apply

Example: Multi-Level Sales Summary

Scenario: Summarize total sales and transaction count by Region AND Customer

Configuration	Value
Grouping Columns:	Region, Customer
Aggregation 1:	Total Sales (Sum of Amount)
Aggregation 2:	Transaction Count (Count Rows)

Result: Each combination of Region/Customer shows total sales and number of transactions.

4. Available Aggregation Functions

Operation	Function	Use Case	M Code
Sum	Adds all values	Total revenue, total quantity	List.Sum([Column])
Average	Calculates mean value	Average price, average score	List.Average([Column])
Median	Finds middle value	Median salary, median price	List.Median([Column])
Min	Finds minimum value	Lowest price, earliest date	List.Min([Column])
Max	Finds maximum value	Highest price, latest date	List.Max([Column])
Count Rows	Counts rows in group	Number of transactions, record count	Table.RowCount(_)
Count Distinct	Counts unique values	Unique customers, distinct products	List.Count(List.Distinct([Column]))
All Rows	Returns grouped rows as table	Keep all data from group	[_]

5. Group By with M Language Code

Basic M Code Structure

= Table.Group(
    #"Previous Step",
    "GroupingColumn",
    {
        {"NewColumnName", each List.Sum([ColumnToAggregate]), type number}
    }
)

Example 1: Group by Single Column with Sum

= Table.Group(
    #"Loaded Data",
    "Product",
    {
        {"Total Sales", each List.Sum([Amount]), type number}
    }
)

Example 2: Group by Multiple Columns with Multiple Aggregations

= Table.Group(
    #"Loaded Data",
    {"Region", "Product"},
    {
        {"Total Sales", each List.Sum([Amount]), type number},
        {"Average Price", each List.Average([Price]), type number},
        {"Transaction Count", each Table.RowCount(_), type number}
    }
)

Example 3: Group by with Distinct Count

= Table.Group(
    #"Loaded Data",
    "Customer",
    {
        {"Unique Products", each List.Count(List.Distinct([Product])), type number},
        {"Total Purchases", each Table.RowCount(_), type number}
    }
)

6. Advanced Use Cases

Scenario 1: Aggregate Text Values

Combine text from multiple rows into a single value:

= Table.Group(
    #"Loaded Data",
    "Customer",
    {
        {"Sales Reps", each Text.Combine(List.Distinct([SalesRep]), ", "), type text}
    }
)

Result: Creates a comma-separated list of unique sales reps for each customer.

Scenario 2: Get All Rows from Group

Keep all rows within each group as a nested table:

= Table.Group(
    #"Loaded Data",
    "Region",
    {
        {"Detail Rows", each _, type table},
        {"Row Count", each Table.RowCount(_), type number}
    }
)

Then expand the nested table to extract specific details.

Scenario 3: Group and Find Top/Bottom Values

Extract top product within each region:

= Table.Group(
    #"Loaded Data",
    "Region",
    {
        {"All Rows", each _, type table},
        {"Top Product", each Text.From(List.Max([Product])), type text}
    }
)

7. Using “All Rows” for Complex Transformations

Step-by-Step Process

1. Create a Group By query using the “All Rows” operation
2. This creates a new column containing a table of all grouped rows
3. Click the expand icon in the column header to expand the nested table
4. Select which columns from the nested data you want to display
5. The result shows your grouping columns plus selected detail columns

Example: Show Top Seller Per Product

1. Group by Product with “All Rows” aggregation
2. Add a custom column that sorts the nested table by sales amount and gets the top row
3. Expand the result to show product and top seller details

8. Grouping Best Practices

Performance Optimization

• Group early: Apply Group By early in your query to reduce data volume for subsequent steps
• Filter before grouping: Remove unnecessary rows before grouping to improve performance
• Use efficient aggregations: Count Rows is faster than Count Distinct; Sum is faster than complex calculations
• Minimize nested tables: Avoid using “All Rows” on large datasets

Data Quality

• Clean before grouping: Remove duplicates and standardize values before grouping
• Handle nulls: Filter out null values that might skew aggregations
• Verify results: Cross-check grouped totals against source data
• Document assumptions: Record what values were excluded or how duplicates were handled

Maintainability

• Name columns clearly: Use descriptive names like “Total Revenue” instead of “Agg1”
• Order grouping columns logically: Group by time period first, then category
• Add comments: Document complex M code for future reference
• Test with sample data: Verify grouping logic before running on full dataset

9. Common Grouping Patterns

Pattern 1: Time-Based Aggregation

Group sales by month and calculate totals:

= Table.Group(
    #"Added Month Column",
    {"Year", "Month"},
    {
        {"Monthly Sales", each List.Sum([Amount]), type number},
        {"Transaction Count", each Table.RowCount(_), type number}
    }
)

Pattern 2: Multi-Dimensional Analysis

Analyze by region, product category, and channel:

= Table.Group(
    #"Loaded Data",
    {"Region", "Category", "Channel"},
    {
        {"Total Sales", each List.Sum([Amount]), type number},
        {"Avg Order", each List.Average([Amount]), type number},
        {"Max Single Sale", each List.Max([Amount]), type number}
    }
)

Pattern 3: Customer Segmentation

Summarize customer purchase behavior:

= Table.Group(
    #"Loaded Data",
    "Customer ID",
    {
        {"Customer Name", each Text.From([Customer]{0}), type text},
        {"Lifetime Value", each List.Sum([Amount]), type number},
        {"Purchase Count", each Table.RowCount(_), type number},
        {"Average Order", each List.Average([Amount]), type number}
    }
)

10. Troubleshooting Group By Issues

Problem: “Grouping column contains nulls”

Solutions:

• Filter out null rows before grouping: Table.SelectRows(#"Previous Step", each [Column] <> null)
• Replace nulls with default value: Table.ReplaceValue(#"Previous Step", null, "Unknown", Replacer.ReplaceValue, {"Column"})

Problem: “Result includes unexpected rows”

Solutions:

• Check for leading/trailing spaces in grouping columns using Text.Trim()
• Verify data types match (text vs. number)
• Check for case sensitivity issues (Group By is case-sensitive by default)

Problem: “Query is very slow”

Solutions:

• Move Group By earlier in your query pipeline
• Filter unnecessary data before grouping
• Use simpler aggregations instead of complex calculations
• Avoid using “All Rows” on large datasets

11. Group By vs. Other Methods

Method	Use When	Advantages	Limitations
Group By	Preparing data for analysis	Flexible, reusable, automatic refresh	Reduces detail to summaries
Pivot Table	Interactive analysis in Excel	Easy to create, flexible layout	Doesn’t persist with data
Excel Formulas	Simple aggregations	Transparent, immediate results	Slow on large datasets

Power Query’s Group By feature is essential for transforming transaction-level data into meaningful summaries. Master these techniques to:

• Aggregate data by single or multiple columns
• Calculate sums, averages, counts, and other metrics
• Combine text values and extract distinct values
• Build complex multi-dimensional analyses
• Automate recurring data transformations

Start with basic grouping using the dialog interface, then progress to advanced M code techniques as you become more comfortable. Always test with sample data, document your grouping logic, and prioritize performance by filtering and grouping early in your query pipeline.