In this post, we look at the List.Average Power Query M function. It is one of the average functions in Power Query and returns the mean average of a list.
Along with detailed notes, there are 10 examples to show how to use List.Average in different contexts.
Table of Contents:
- Purpose
- Return Value
- Syntax
- Parameters
- Usage Notes
- Examples
- Example #1: Average items in a list
- Example #2: Average from a separate list query
- Example #3: Average from a single table column
- Example #4: Average from multiple lists
- Example #5: Average with different data types
- Example #6: Comparison of 3 types of average (mean, median, mode)
- Example #7: Average different number types
- Example #8: Average with increased levels of precision
- Example #9: List.Average with null vs. 0
- Example #10: Average an empty list
- Related Tutorials
- Related Functions
Purpose
Returns the mean average of non-null values from a list; or returns null where there are no non-null values.
The list provided can be numbers, dates, time, datetime, datetimezone, or duration data types.
Return Value
A number, date, time, datetime, datetimezone or duration (dependent on the data type of the input) representing the mean average of the non-null items from a list.
Alternatively, the return value is null if the list contains no non-null items.
Syntax
List.Average(list as list, optional precision as nullable number) as anyParameters
- list – A Power Query list. The list can contain number, date, datetime, datetimezone, or duration data types. The list can be:
- Individual comma-separated items contained within curly brackets, e.g. {#date(2022,12,31), #date(2023,01,17), #date(2023,04,23)}.
- Existing query or step with a list type as an output.
- Function which returns a list, e.g., List.Union
- precision – an optional argument defining the accuracy required in the result. The values can be:
- Precision.Double or 0 – applies standard precision based on the IEEE 754 standard. This can be up to 18 digits. This is the default if no argument is provided.
- Precision.Decimal or 1 – higher level of precision. It can be up to 29 digits. Requires more memory for additional accuracy.
Usage Notes
The List.Average function is in the List function category and evaluates down to a single value from a supplied List.
List.Average is available in two locations in the standard user interface:
Transform [1] > Statistics [2] > Average [3]
List Totals [1]: Statistics [2] > Average [3]
Examples
Example #1: Average items in a list
The list parameter can be a standard M code list. This is a comma-separated list held within curly brackets.
The function below averages 4 numbers.
= List.Average({1, 2, 3, 4})Returns: 2.5
Example #2: Average from a separate list query
Where the output of another query or step is a list, that list can be used as the list parameter within the function.
The following query, called Example List, has a list output.
= {1, 2, 3, 4}The list query above can be used inside the List.Average function.
= List.Average(#"Example List")Returns: 2.5
Example #3: Average from a single Table column
A single table column is a list. Therefore, this can be the list parameter supplied to the function.
The table above is named Example Table. The code below calculates the mean of the Value column.
= List.Average(#"Example Table"[Value])Returns: 25
Example #4: Average from multiple lists
If the average of multiple lists is required, List.Combine can be used before calculating the average with List.Average.
The example below returns the mean average of the Example List query from Example 2 and the numbers 5 and 6.
= List.Average(List.Combine({#"Example List",{5, 6}}))List.Combine evaluates to: {1,2,3,4,5,6}.
The result of List.Combine is calculated inside List.Average.
Returns: 3
Example #5: Average with different data types
List.Average handles multiple data types: numbers, date, datetime, datetimezone, and duration.
In the example below, the mean average is calculated across 3 dates:
- 31 December 2022
- 17 January 2023
- 23 April 2023
= List.Average({#date(2022,12,31), #date(2023,01,17), #date(2023,04,23)})Returns: In my locale, it returns 12/02/2023 (i.e. 12 February 2023) which is #date(2023,02,12)
All data types in the calculation must be the same type; otherwise, Power Query returns an error. In the example below, number and date data types are included within the list.
=List.Average({1, #date(2023,01,17), #date(2023,04,23)})
Returns: Expression.error: We cannot apply operator – to types Date and Number.
Example #6: Comparison of 3 types of average (mean, median, mode)
In basic mathematics, there are 3 different types of averages; Mean, Median and Mode.
In this section, we compare each calculation.
Mean Average: List.Average
The mean average is the sum of all the items divided by the count of the items.
= List.Average({1, 2, 3, 3, 4, 5, 3, 2, 3, 2, 3, 3, 5})Returns: 3
Median Average: List.Median
The median average is the middle value from the sorted list of values. Where there is an even number of items, the result is the mean average of the middle two values.
= List.Median({1, 2, 3, 3, 4, 5, 3, 2, 3, 2, 3, 3, 5})The sorted list of numbers is as follows:
1, 2, 2, 2, 3, 3, 3, 3, 3, 3, 4, 5, 5
The median is the middle value: 1, 2, 2, 2, 3, 3, 3, 3, 3, 3, 4, 5, 5
Returns: 3
Mode Average: List.Mode
The mode average is the value that appears the most.
= List.Mode({1, 2, 3, 3, 4, 5, 3, 2, 3, 2, 3, 3, 5})We can break out the list as follows:
- 1
- 2, 2, 2
- 3, 3, 3, 3, 3, 3
- 4
- 5, 5
Returns: 3
In our examples, List.Average, List.Median and List.Mode all evaluated the same value. However, their approach is very different. Therefore, dependent on the values can provide significantly different results.
Example #7: Average different number types
While we cannot average different data types in Power Query, there are different number formats:
- Literal: 1
- Decimal: 1.2
- Exponential: 1e2 (= 100)
- Hexidecimal: 0x0A (= 10)
Power Query treats these formats as numbers and can be included within List.Average.
= List.Average({1, 1.2, 1e2, 0x0A})Returns: 28.05
For further resources on numbers in Power Query, check out this post from Ben Gribaudo: Power Query M Primer (Part 7): Types—Numbers.
Example #8: Average with increased precision
The precision parameter in Power Query allows the number of significant digits to be increased.
- Precision.Double: up to 18 significant digits (default if parameter not provided)
- Precision.Decimal: up to 29 digits
In the following example, Precision.Double returns a recurring decimal to 17 digits.
= List.Average({0, 1, 2, 3, 2, 2},Precision.Double)Returns: 1.6666666666666667
By changing the last argument to Precision.Decimal, the precision increases to 29 digits.
= List.Average({0, 1, 2, 3, 2, 2},Precision.Decimal)Returns: 1.6666666666666666666666666667
Example #9: List.Average with null vs 0
List.Average includes only the non-null items. Therefore, it is essential to understand the different Power Query treatments between 0 and null values.
The example below includes null in the list. However, null is excluded before undertaking the calculation.
= List.Average({null, 1, 2, 3})Returns: 2
The following example includes 0. Therefore this is included in the calculation.
= List.Average({0, 1, 2, 3})Returns: 1.5
These examples demonstrate the importance of ensuring the data includes the correct values before using List.Average.
Example #10: Average an empty list
With List.Average, an empty list, and a list aggregating to zero are not the same.
The formula below evaluates to zero.
= List.Average({1, -1})Returns: 0
The formula below evaluates to null because the list is empty.
= List.Average({})Returns: null
This demonstrates that 0 and null are not the same result.
Related Tutorials
List.Average is used in the following Tutorials:
Related Functions
- None