Grouping and Aggregating Data in Power Query: Summarize, Count, and Calculate with Group By

Introduction

Imagine you've just connected Power Query to a sales database with 50,000 rows — one row per transaction. Your manager doesn't want to see 50,000 rows. They want to know total revenue by region, how many orders each customer placed last quarter, and which product category generated the most profit. That transformation — from raw, granular records to meaningful summaries — is called aggregation, and it's one of the most important skills in data analysis.

Power Query's Group By feature is the tool that makes this happen. It lets you collapse rows that share a common value (like the same region, the same customer, or the same product category) and calculate something meaningful across all those rows (like a sum, a count, or an average). If you've ever used a GROUP BY clause in SQL or a SUMIF formula in Excel, you're familiar with the concept — but Power Query's visual interface makes it more approachable, and its M language gives you more flexibility than you might expect.

By the end of this lesson, you'll know how to use Group By to summarize datasets in multiple ways, how to apply several aggregations at once, and how to troubleshoot the errors that trip up beginners. You'll also build a real working example you can adapt immediately for your own work.

What you'll learn:

• What grouping and aggregation mean, and why they matter
• How to use the Group By dialog in the Power Query Editor
• How to apply multiple aggregations simultaneously (sum, count, average, min, max)
• How to use the underlying M code that Group By generates
• How to handle common errors and edge cases

---

Prerequisites

You should be comfortable with the basics of opening Power Query and navigating the Editor. Specifically, you should know how to:

• Connect to a data source (Excel file, CSV, or any other source)
• Open the Power Query Editor from Power BI Desktop or Excel
• Apply basic steps like filtering rows or changing data types

If any of those feel shaky, work through the earlier lessons in this learning path first. Group By makes much more sense once you understand what a "step" is and how the Applied Steps pane works.

---

What "Grouping" Actually Means

Before touching any buttons, let's make sure the underlying concept is solid, because rushing past it causes a lot of confusion later.

Think about a spreadsheet of coffee shop sales. Each row represents one sale:

Date        | Store      | Barista  | Item        | Revenue
2024-01-03  | Downtown   | Maria    | Latte       | 5.50
2024-01-03  | Downtown   | Maria    | Cappuccino  | 5.00
2024-01-03  | Uptown     | James    | Espresso    | 3.50
2024-01-04  | Downtown   | Carlos   | Latte       | 5.50
2024-01-04  | Uptown     | James    | Latte       | 5.50

When you group by Store, you're telling Power Query: "Find all the rows that have the same Store value and treat them as a single group." The Downtown rows form one group; the Uptown rows form another.

Once you have those groups, you need to decide what to do with each group — that's the aggregation. Common options include:

• Sum — Add up all the Revenue values in each group
• Count — Count how many rows exist in each group
• Average — Calculate the mean Revenue per group
• Min / Max — Find the smallest or largest value in each group

The result is a new, smaller table — one row per group — with whatever aggregated columns you asked for. The original 50,000-row table becomes a 5-row summary. That's the power of Group By.

---

Setting Up a Sample Dataset

Let's use a realistic scenario throughout this lesson. Download or create a CSV file called sales_transactions.csv with these columns:

• OrderID — a unique identifier for each transaction
• Region — one of: North, South, East, West
• SalesRep — the name of the salesperson
• ProductCategory — one of: Electronics, Clothing, Food, Tools
• OrderDate — the date of the sale
• Quantity — number of units sold
• UnitPrice — price per unit
• TotalSale — Quantity × UnitPrice

You can build this in Excel with a few dozen rows, or use any similar dataset you already have on hand. The columns that matter most for this lesson are Region, ProductCategory, SalesRep, TotalSale, and Quantity.

To load it into Power Query from Excel, go to the Data tab on the ribbon, click Get Data, choose From File, then From Text/CSV, and navigate to your file. In Power BI Desktop, click Get Data on the Home tab, choose Text/CSV, and select the file. Once the preview loads, click Transform Data to open the Power Query Editor instead of loading directly to the sheet or model.

---

Using the Group By Dialog: Your First Aggregation

Now you're inside the Power Query Editor with your data loaded. Let's create a simple summary: total sales revenue per Region.

On the Home tab of the Power Query Editor ribbon, click the Group By button. (In some versions of Power BI, you'll find it under the Transform tab as well.) A dialog box will open.

The dialog has two main sections:

1. Group by columns — What should the groups be based on?
2. New column aggregations — What should be calculated for each group?

Step 1: Choose Your Grouping Column

At the top of the dialog, you'll see a dropdown labeled "Group by." Click it and select Region. This tells Power Query that every unique Region value should become its own row in the output.

You can group by multiple columns at once — we'll do that shortly. For now, stick with one.

Step 2: Define the Aggregation

Below the grouping section, you'll see a row for your new aggregated column. It has three parts:

• New column name — What you want to call the output column. Type Total Revenue.
• Operation — The type of aggregation. Click the dropdown and choose Sum.
• Column — Which column to sum. Choose TotalSale.

Step 3: Apply

Click OK. Power Query immediately collapses your table down to one row per Region, with a new Total Revenue column showing the sum of all TotalSale values for that region.

What you'll see in the Applied Steps pane on the right is a new step called Grouped Rows. Every action in Power Query creates a step, and this one records exactly what you just did.

Tip: Always rename your aggregated column to something meaningful before clicking OK. The default names like "Sum of TotalSale" are functional but ugly — they'll follow you all the way into your reports.

---

Adding Multiple Aggregations at Once

One aggregation per group is often not enough. You probably want to know total revenue and number of orders and average order size all in the same summary table. Power Query lets you add as many aggregations as you need in a single Group By step.

Go back to your original data (click the previous step in the Applied Steps pane, or undo the last step). Now open Group By again.

This time, click the Add aggregation button at the bottom of the dialog to add more rows. Set up these three aggregations:

New Column Name	Operation	Column
Total Revenue	Sum	TotalSale
Order Count	Count Rows	(none)
Avg Order Value	Average	TotalSale

A note on Count Rows: this is different from other aggregations. It doesn't operate on a specific column — it simply counts how many rows existed in each group. This is equivalent to COUNT(*) in SQL. You'll notice that when you select Count Rows as the operation, the Column dropdown grays out. That's expected.

Click OK and you'll get a compact summary table with all three new columns alongside Region. This is far more useful than running three separate steps.

---

Grouping by Multiple Columns

Now let's go a level deeper: what if you want revenue broken down by both Region and ProductCategory? That means you need a row for every unique combination of Region and ProductCategory.

Open Group By again. At the top of the dialog, you'll see your first grouping column (Region). Look for an Add grouping button and click it — a second dropdown appears. Select ProductCategory.

Keep your aggregations the same as before (Total Revenue, Order Count, Avg Order Value), then click OK.

Your output will now have one row for each Region + ProductCategory combination. If you have 4 regions and 4 product categories, you'll see up to 16 rows — fewer if some combinations don't exist in your data.

Warning: When grouping by multiple columns, the number of output rows grows quickly. Grouping by 3 columns with 10 unique values each could give you up to 1,000 rows. Make sure you're grouping by the right level of granularity for your analysis.

---

Understanding the M Code Behind Group By

Every step you take in the Power Query Editor is translated into M, Power Query's formula language. You don't need to write M to use Group By, but understanding what it generates helps you troubleshoot problems and make tweaks that the dialog doesn't easily support.

After creating a Group By step, click on the step in the Applied Steps pane and look at the formula bar at the top of the Editor. You'll see something like this:

= Table.Group(
    #"Changed Type",
    {"Region", "ProductCategory"},
    {
        {"Total Revenue", each List.Sum([TotalSale]), type number},
        {"Order Count", each Table.RowCount(_), type number},
        {"Avg Order Value", each List.Average([TotalSale]), type number}
    }
)

Let's break this down:

• Table.Group is the M function that performs grouping. It takes three arguments.
• #"Changed Type" is the name of the previous step — this is what the function is acting on.
• {"Region", "ProductCategory"} is a list of column names to group by.
• The third argument is a list of aggregations, where each entry has three parts: the output column name, the aggregation logic (written as a function using each), and the output data type.

The each keyword is shorthand for creating a small function. each List.Sum([TotalSale]) means "for each group, sum the TotalSale column." The underscore _ in Table.RowCount(_) refers to the entire sub-table for that group.

You can edit this M code directly if you need to do something the dialog doesn't support — for example, calculating a weighted average or combining two columns in a custom formula.

Tip: If you ever want to add a custom aggregation that the dialog doesn't offer (like a median, which isn't available by default), you can write it directly in M. each List.Median([TotalSale]) works perfectly and can be added as a fourth item in the aggregations list.

---

Handling Data Type Issues

One of the most common frustrations with Group By is getting an error that says something like Expression.Error: We cannot convert the value null to type Number. This almost always comes down to data types.

Before you run Group By, make sure your numeric columns are actually typed as numbers. In the Power Query Editor, look at the column header — there's a small icon on the left side of the header that shows the data type. A "123" icon means integer, a "1.2" icon means decimal number, an "ABC" icon means text.

If your TotalSale column is typed as Text (which happens often when importing from CSV), Power Query can't sum it. You'll get an error immediately or, worse, a silent wrong result.

Fix this before Group By by clicking the data type icon in the column header and selecting the correct type — usually Decimal Number for currency or revenue values.

Null values are another issue. If some rows in your TotalSale column are blank (null), List.Sum will still work — it ignores nulls. But List.Average will include only non-null values in its calculation, which may or may not be what you want. Be intentional about whether you want to replace nulls before aggregating.

---

Advanced: All Rows and Nested Tables

Here's something that surprises many beginners: Group By doesn't have to produce a flat summary. There's an operation in the dropdown called All Rows that behaves very differently from Sum or Count.

When you use All Rows, Power Query keeps the entire sub-table for each group as a nested value inside a single cell. Instead of collapsing the rows into a number, you get a Table object.

Why would you want this? Two common reasons:

1. You want to keep the detail and add a group-level calculation alongside it. For example, you could group by SalesRep, collect All Rows, then expand the table back out — but this time with the group total attached to every row. This is how you'd calculate "what percentage of regional revenue did each order represent?"

2. You need to do further processing on each group before flattening. For instance, you might want the top 3 orders per region, which requires sorting within each group before taking rows.

The All Rows technique is more advanced and covered in depth in a later lesson on custom aggregations. For now, just know it exists — and that the flat aggregations (Sum, Count, Average) are what you'll use 90% of the time.

---

Hands-On Exercise

Let's put everything together in one structured exercise. Use your sales_transactions.csv dataset (or any similar data you have available).

Your goal: Create a summary table that shows, for each SalesRep, the total number of orders they placed, their total revenue, and their single highest-value order.

Steps:

1. Open your file in Power Query. Make sure TotalSale is typed as a Decimal Number.

2. Click Group By on the Home tab.

3. In the "Group by" section, select SalesRep.

4. Add three aggregations:

   • New column name: Total Orders | Operation: Count Rows
   • New column name: Total Revenue | Operation: Sum | Column: TotalSale
   • New column name: Best Order | Operation: Max | Column: TotalSale

5. Click OK.

6. Sort the resulting table by Total Revenue descending: click the dropdown arrow on the Total Revenue column header and choose Sort Descending.

7. Rename this query to SalesRep Summary by right-clicking the query name in the Queries pane on the left and choosing Rename.

8. Click Close & Load (in Excel) or Close & Apply (in Power BI) to load the result.

You should now have a clean summary table with one row per salesperson. Notice how what started as potentially thousands of rows became a compact leaderboard in a few clicks.

Bonus challenge: Go back into the query, add a second grouping column for ProductCategory, and see how the results change. You're now looking at each salesperson's performance broken down by what they actually sold.

---

Common Mistakes & Troubleshooting

Mistake 1: Aggregating a text column with Sum If you try to Sum a column that contains text, Power Query will throw an error. The fix is to check and correct the data type before running Group By.

Mistake 2: Forgetting that Group By removes ungrouped columns This trips up almost everyone the first time. When you group by Region and aggregate TotalSale, the output only contains Region plus your new aggregated columns. The OrderID, SalesRep, OrderDate, and all other columns disappear because they're neither a grouping key nor an aggregation. If you need those columns in your result, you either need to include them as grouping keys or use a merge (join) after the fact to bring them back.

Mistake 3: Grouping before cleaning the data If your Region column contains "North", "north", and "NORTH" as distinct values, Group By will treat them as three different groups. Always normalize text case and trim whitespace before grouping. Use Transform → Format → Trim and Transform → Format → Capitalize Each Word (or UPPERCASE/lowercase) to standardize values first.

Mistake 4: Misunderstanding Count vs. Count Rows The dialog offers both "Count" and "Count Rows" as options. Count Rows counts all rows in the group regardless of content — it's the one you want most of the time. Count (also called Count Distinct in some versions) counts non-null or distinct values in a specific column. Make sure you pick the right one for your question.

Mistake 5: Not refreshing after source data changes If you load your summary to Excel or Power BI and then the source data changes, your Group By result won't update automatically until you refresh the query. In Excel, right-click the output table and choose Refresh. In Power BI, click the Refresh button on the Home tab.

---

Summary & Next Steps

Group By is one of the highest-leverage features in Power Query. What you've learned in this lesson:

• Grouping collapses rows that share a common value into single groups; aggregation calculates something meaningful across each group
• The Group By dialog lets you set grouping columns and define multiple aggregations (Sum, Count Rows, Average, Min, Max) visually
• You can group by multiple columns to get more granular breakdowns
• Power Query generates M code (Table.Group) that you can read and edit directly
• Data type errors and inconsistent text values are the most common sources of problems — clean before you group
• The All Rows operation is a gateway to more advanced aggregation patterns covered in later lessons

Where to go next:

• Merging Queries — Learn how to join your summary table back to detailed data, which lets you add group-level context (like "% of total") to row-level records
• Custom Columns — Use the Add Column menu to create calculated fields (like Revenue Per Unit) before or after grouping
• Advanced M: Custom Aggregations — Write M directly to calculate medians, percentiles, or weighted averages that the dialog doesn't offer
• Conditional Aggregations — Filter rows before grouping using Table.SelectRows inside a custom aggregation to build the Power Query equivalent of SUMIF

The Group By feature you practiced here will show up in almost every real-world Power Query project you take on. Master it now and you'll find yourself reaching for it constantly.