Aggregation
A calculation over many values that returns one, such as sum, count, minimum, maximum or average.
Aggregations ignore nulls in most cases: the average of {1, null, 3} is 2. Count Rows counts rows regardless of values, while counting non-null values needs List.NonNullCount. Choosing the right aggregation is part of defining what a report number means.
In the Power Query editor
The Operation list in the Group By dialog and the aggregation choice in Pivot Column.
Common mistake
Using Count Rows when the question is how many customers, orders or valid readings there are. Count Rows counts every row in the group, including rows with null in the column you care about and repeated values. Count Distinct Rows, or List.Count(List.Distinct(...)) on one column, answers how many different values there are, and List.NonNullCount answers how many values were actually filled in. Write down which question the number answers before picking the operation.
Learn more
Practise it
- intermediate~8 minPro
Summarise orders by region (kite shop)
Group by Region with order count, total quantity and average unit price, sorted by region.
- intermediate~8 minPro
Count distinct customers per product (kite shop)
For each product, count how many different customers ordered it.
- advanced~10 minPro
Show each region's share of total revenue (kite shop)
Total revenue per region, then each region's percentage of the grand total.
- intermediate~8 minPro
Find each region's first and last order date (kite shop)
Group by Region with the earliest and latest OrderDate as real dates.