10.7 Group By
Group By in Power Query with example — in 30 seconds
What: Group By makes one row per group with totals, counts or averages, like a pivot saved as a query step.
Why: A small summary table loads faster into the dashboard than thousands of raw rows.
Tiny example: Delivered orders grouped by City give 4 rows with Total Sales and Orders.
Group By in Power Query with example — 30 सेकंदात
काय: Group By प्रत्येक group साठी एक row बनवतो, त्यात totals, counts किंवा averages असतात, query step म्हणून save केलेल्या pivot सारखं.
का: हजारो raw rows पेक्षा छोटं summary table dashboard मध्ये लवकर load होतं.
छोटं उदाहरण: Delivered orders City नुसार group केल्या की Total Sales आणि Orders सह 4 rows मिळतात.
Group By in Power Query with example — 30 सेकंड में
क्या: Group By हर group के लिए एक row बनाता है जिसमें totals, counts या averages होते हैं, query step की तरह save किए pivot जैसा.
क्यों: हज़ारों raw rows से छोटी summary table dashboard में जल्दी load होती है.
छोटा उदाहरण: Delivered orders को City से group करने पर Total Sales और Orders के साथ 4 rows मिलती हैं.
First look at the data (before → after):
Expected result — Grouped query
| City | Total Sales | Orders |
|---|---|---|
| Kolhapur | 232 | 1 |
| Nashik | 360 | 2 |
| Pune | 324 | 3 |
| Solapur | 1299 | 1 |
About this data: Teaching sample for a fictional shop “Maple Bakery Co”. Figures and names (e.g. Rohan Patil) are made up for this lesson on group by.
Before table and formula as text
Before — Delivered rows of tblMini
| Order ID | City | Amount |
|---|---|---|
| BLK-1001 | Pune | 64 |
| BLK-1002 | Nashik | 270 |
| AMN-1003 | Pune | 110 |
| AMN-1005 | Kolhapur | 232 |
| BLK-1006 | Pune | 150 |
| BLK-1007 | Solapur | 1299 |
| BLK-1010 | Nashik | 90 |
About this data: Demo table only — not real customer or company data. Built to practise group by.
Formula / click path
Filter Status = Delivered
Transform › Group By › Advanced: City
Total Sales = Sum of Amount · Orders = Count Rows
Do it step by step:
- In Power Query, filter Status to Delivered.
- Click Transform › Group By and choose Advanced.
- Group by City.
- Add
Total Sales= Sum of Amount, then click Add aggregation and addOrders= Count Rows. - Click OK and check that Total Sales adds up to 2215.
स्टेप बाय स्टेप करून बघा:
- Power Query मध्ये Status ला Delivered filter करा.
- Transform › Group By वर click करा आणि Advanced निवडा.
- City नुसार group करा.
Total Sales= Amount चा Sum टाका, मग Add aggregation करूनOrders= Count Rows टाका.- OK करा आणि Total Sales ची बेरीज 2215 येते का ते check करा.
स्टेप बाय स्टेप करके देखें:
- Power Query में Status को Delivered filter करें.
- Transform › Group By पर click करें और Advanced चुनें.
- City से group करें.
Total Sales= Amount का Sum डालें, फिर Add aggregation करकेOrders= Count Rows डालें.- OK करें और check करें कि Total Sales का जोड़ 2215 आता है.
Transform › Table › Group By summarises rows (like a PivotTable, but the output is a table you can merge or load).
Steps in Excel
- Select
All_Orders› Transform › Table › Group By. - Basic: Group by City › New column
Total Sales› Operation Sum › Column Amount. - Advanced: Group by City and Platform › add aggregations:
Orders= Count Rows;Avg Mins= Average of Delivery Mins;Max Order= Max of Amount. (Count Distinct Rows counts distinct whole rows of the group – for distinct customers, first keep only City, Platform and Customer columns in a reference query.) - Close & Load to a sheet or to the Data Model.
Worked example (mini dataset of Module 3):
| City | Platform | Total Sales | Orders |
|---|---|---|---|
| Pune | FreshGo | 434 | 3 |
| Pune | MarketRush | 110 | 1 |
| Nashik | FreshGo | 360 | 2 |
| Nagpur | FreshGo | 240 | 1 |
| Kolhapur | MarketRush | 232 | 1 |
| Solapur | FreshGo | 1,299 | 1 |
| Sambhaji Nagar | MarketRush | 195 | 1 |
About this data: Made-up rows so you can copy the pattern for group by. Currencies may use ₹, £, € or $.
Ravindra Bagale's Tip
After Group By, all the other columns (Date, Product) disappear – many students panic: "the data is gone!" Group By keeps only the chosen group columns and aggregations; the detail data is safe in the original query. If you need the detail, create a Reference query and do the Group By on that.
Ravindra Bagale's Tip – मराठी
Group By नंतर बाकी सगळे columns (Date, Product) नाहीसे होतात – बरेच students घाबरतात "data गेला!". Group By फक्त निवडलेले group columns आणि aggregations ठेवतो; detail data original query मध्ये सुरक्षित आहे. Detail हवी असेल तर Reference query बनवा आणि त्यावर Group By करा.
Ravindra Bagale's Tip – हिंदी
Group By के बाद बाकी सारे columns (Date, Product) गायब हो जाते हैं – बहुत से students घबरा जाते हैं "data चला गया!". Group By सिर्फ़ चुने गए group columns और aggregations रखता है; detail data original query में सुरक्षित है. Detail चाहिए तो Reference query बनाओ और उस पर Group By करो.
Worked example — group by
Why
Sana Sheikh in Pune and Oliver Bennett abroad both need a clear, repeatable way to use group by on a Maple Bakery Co sheet without breaking totals.
What
Group By is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for group by.
- Enter a small made-up value for Sana Sheikh using ₹ where money appears.
- Add a second value for Oliver Bennett using $ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once group by is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of group by on a blank sheet for HomeNest Supplies.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Sana Sheikh and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Sophie Martin.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply group by to a 5-row made-up HomeNest Supplies table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with group by.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as HomeNestSup_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Rows in the import (Easy): How many rows are in the Order ID column?
- Clean one customer name (Easy): Remove the extra spaces from the first Customer name.
- Fix the case of a city (Easy): Show the first City in normal case, like "Pune".
- Total quantity (Easy): Add up the Qty column.
- How many spaces were extra? (Medium): How many extra spaces does the first Customer name have?
- Duplicate order check (Medium): How many times does Order ID "ORD-502" appear?
- Orders from one city, any case (Medium): How many rows have City "Singapore", in any case?
- Amount by channel (Medium): Add up the Amount column for rows where Channel is "App".
- Average order amount (Medium): Find the average of the Amount column, rounded to 0 decimals.
- Rows after Remove Duplicates (Hard): How many rows are left after the extra copies of "ORD-502" are removed?
- Biggest store order (Hard): What is the largest Amount where Channel is "Store"?
- Price per unit (Hard): For the first row, divide Amount by Qty.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
