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
Group By nantar baki sagle columns (Date, Product) nahise hotat – khup students ghabartat "data gela!". Group By fakt nivadlele group columns aani aggregations thevto; detail data original query madhe surakshit aahe. Detail havi asel tar Reference query banva aani tyavar Group By kara.
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.