10.7 Group By
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 | Blinkit | 434 | 3 |
| Pune | Amazon Now | 110 | 1 |
| Nashik | Blinkit | 360 | 2 |
| Nagpur | Blinkit | 240 | 1 |
| Kolhapur | Amazon Now | 232 | 1 |
| Solapur | Blinkit | 1,299 | 1 |
| Sambhaji Nagar | Amazon Now | 195 | 1 |
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 करो.
Practice task
Using Group By, create a daily summary per store: orders, sales, average delivery time and maximum order value. Load it to a sheet and chart the daily sales.