Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

7.23 Group By (Basic and Advanced)

By Last updated:

Start with a problem

The target team needs one row per city with total sales, order count and average delivery time – not 8 order rows.

Explain simply

Group By creates one row per group with aggregations (Sum, Count Rows, Average, Min, Max, All Rows). Advanced allows several groups and several aggregations.

Show the data

Order ID Order Date City Store Platform Category Amount (₹) Delivery mins Status
301 06-Apr-2026 Pune Kothrud Blinkit Dairy 400 8 Delivered
302 06-Apr-2026 Pune Baner Amazon Now Snacks 200 12 Delivered
303 07-Apr-2026 Nashik Gangapur Blinkit Dairy 300 14 Delivered
304 04-May-2026 Nashik College Road Blinkit Fruits 150 9 Delivered
305 04-May-2026 Kolhapur Rajarampuri Amazon Now Snacks 450 7 Delivered
306 05-May-2026 Pune Kothrud Blinkit Fruits 250 16 Delivered
307 05-May-2026 Nashik College Road Amazon Now Dairy 350 11 Delivered
308 05-May-2026 Kolhapur Rajarampuri Blinkit Dairy 0 Cancelled

About this data: This table uses columns: Order ID, Order Date, City, Store, Platform, Category, Amount (₹), Delivery mins, Status. This table uses columns: City, Sales. This table uses columns: City, Orders, Avg Mins. This table uses columns: City, Sales (₹), Orders, Avg Mins. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Transform › Group By › Basic › City › New column Sales = Sum of Amount.

    Your result should look like this:

    City Sales
    Pune 850
    Nashik 800
    Kolhapur 450

  2. Edit the step › Advanced › add Orders = Count Rows, Avg Mins = Average of Delivery Mins.

    Your result should look like this:

    City Orders Avg Mins
    Pune 3 12.0
    Nashik 3 11.33
    Kolhapur 2 7.0

  3. Advanced with City and Platform.

    Your result should look like this:

    Rows
    6

Show the result

Group By city with sum, count and average, sample layout with fictional data

Your result should look like this (fictional sample):

City Sales (₹) Orders Avg Mins
Pune 850 3 12.0
Nashik 800 3 11.33
Kolhapur 450 2 7.0

Explain what happened

Group By uses List.Sum, Table.RowCount and List.Average; List.Average ignores null, so Kolhapur's average uses only order 305.

Explore further

  • Use Group By in Power Query only when you truly need a smaller table; otherwise use measures.

Detailed reference for this lesson:

Group By in Power Query with example — in 30 seconds

What: Group By makes one row per group and adds a sum, count or average for each group.

Why: Sometimes the source is too big. You only need city totals for a target table.

Tiny example: 5 order rows become 2 city rows: Pune ₹1,000 (3 orders) and Mumbai ₹1,100 (2 orders).

First look at the data (before → after):

Group By in Power Query with example: before and after tables, fictional Blinkit Maharashtra data

Same before / after tables as text

Before — Order rows

Order ID City Amount (₹)
BLK-1 Pune 500
BLK-2 Pune 300
BLK-3 Pune 200
BLK-4 Mumbai 400
BLK-5 Mumbai 700

Formula / click path

Transform › Group By · Group: City
New columns: Total = Sum of Amount · Orders = Count Rows

After — After Group By

City Total (₹) Orders
Pune 1,000 3
Mumbai 1,100 2

Do it step by step:

  1. Click Transform › Group By.
  2. Choose Advanced. Group by City.
  3. New column 1: name Total, operation Sum, column Amount.
  4. Click Add aggregation. Name Orders, operation Count Rows.
  5. Click OK. Keep the detailed query too if a report needs order-level drill.

Before (order lines)

City Order ID Amount
Pune BLK-1 120
Pune BLK-1 80
Pune BLK-2 300
Nashik BLK-3 150

After (grouped by City)

City Total Sales Order Lines Orders
Pune 500 3 2
Nashik 150 1 1

Steps in Power BI

  1. Home › Group By (or Transform › Group By).
  2. Basic: Group by City; New column name Order Lines; Operation Count Rows › OK.
  3. Advanced: choose Advanced › Add grouping (City, Platform) › Add aggregation: Total Sales = Sum of Amount; Order Lines = Count Rows; Avg Delivery = Average of Delivery Time Mins; Details = All Rows.
  4. Distinct orders are not a direct option for one column. Add an aggregation (any column), then edit its formula in the formula bar to each List.Count(List.Distinct([Order ID])).
Grouped = Table.Group(Source, {"City"}, {
    {"Total Sales", each List.Sum([Amount]), type number},
    {"Order Lines", each Table.RowCount(_), Int64.Type},
    {"Orders",      each List.Count(List.Distinct([Order ID])), Int64.Type}
})

Simple bhashet sangaycha tar, available operations: Sum, Average, Median, Min, Max, Percentile, Count Rows, Count Distinct Rows, All Rows. (Count Distinct Rows counts distinct whole rows of the group, not distinct values of one column, which is why step 4 is needed.) The list may differ slightly between versions.

Grouping loses detail

Once grouped, the detailed rows are gone from that query. Usually you load detailed data and let DAX measures aggregate (Module 13). Group in Power Query fakt for a real summary table, a data-quality count, or to reduce a khup large table.

Practice task

Group Orders by Store ID and Order Date to create a Daily Store Summary with Orders, Total Sales and Max Delivery Time Mins.

Ravindra Bagale's Tip

Friends, many students group by too many columns and get nearly the original table back, or they use Sum on a column that is still text. Decide the output grain first ("one row per store per day"), set types before grouping, and use Count Distinct Rows when you need unique orders. Don't make this mistake!

Fix common mistakes

Wrong result Reason Correction
Kolhapur average 3.5 Null replaced with 0 before grouping Keep null
Lost order detail Grouped the main fact table Group a Reference copy
Counts differ from report Count Rows includes cancelled Filter first or count distinct delivered

Practise independently

Practice task

Group by Platform: Sum of Amount and Count Rows.

Hints
  • Blinkit has 5 rows.
Solution

Blinkit ₹1,100 (5 orders), Amazon Now ₹1,000 (3 orders).

Think like a professional

Pre-aggregating is a performance choice – explain what detail you lose.

Ravi Sir's Cards for this lesson

Group By: basic and advanced. Sometimes you need a summary table, like one row per city.Revision card

मराठी cards