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
Mitrano, khup 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. Chuk karu naka!