7. Data Cleaning A–Z in Power Query
7.23 Group By (Basic and Advanced)
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
- Home › Group By (or Transform › Group By).
- Basic: Group by City; New column name
Order Lines; Operation Count Rows › OK. - 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. - 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!
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students खूप जास्त columns वर group by करतात आणि जवळजवळ original table च परत मिळवतात, किंवा अजून text असलेल्या column वर Sum वापरतात. आधी output grain ठरवा ("one row per store per day"), grouping आधी types set करा, आणि unique orders हवे असतील तर Count Distinct Rows वापरा. ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students बहुत ज़्यादा columns पर group by करते हैं और लगभग original table ही वापस पाते हैं, या ऐसे column पर Sum लगाते हैं जो अभी भी text है. पहले output grain तय करो ("one row per store per day"), grouping से पहले types set करो, और unique orders चाहिए तो Count Distinct Rows इस्तेमाल करो. यह गलती मत करना!