Skip to content

List.Average in Power Query: 10 easy examples

List.Average featured image

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

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 any

Parameters

  • 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.Average included in standard user interface

List Totals [1]: Statistics [2] > Average [3]

List.Average in List Tools

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.

List Query example data
= {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.

Example Table for List.Sum

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)})
List.Average error with mixed data types

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