Power QueryExercises

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

Related terms