List.Average - List Function

List.Average is a Power Query M function that calculates the average value for items in a list. The function returns the average value in the same datatype as the list’s values, working with number, date, time, datetime, datetimezone, and duration values.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Average(
   list as list,
   optional precision as nullable number,
) as any

Argument	Attribute	Description
list		This is the list of values to compute an average of.
precision	optional	The Precision.Type specifies the accuracy level for calculations. When omitting this argument, Power Query uses Precision.Double by default, which, while efficient, may cause rounding errors with very small fractions. For greater accuracy Precision.Decimal offers a more precise number representation, ideal for tasks requiring high precision.

Description

The List.Average function takes a list of values and computes the average of these. It works with a range of values. It will return a valid outcome if you provide a list containing any of the following values as input:

Number: any number value will work, whether whole numbers or with decimals.
Date: specified using the #date function.
Time: specified using the #time function.
DateTime: specified using the #datetime function.
DateTimeZone: specified using the #datetimezone function.
Duration: specified using the #duration function.

Depending on the type of value specified, the outcome will be of the data type provided in the list. Lastly, for empty lists a null value is returned.

Examples

Let’s look at some examples to put the List.Average function to work.

Averaging Numeric Values

To begin with the basics, List.Average can calculate the average of a list of numeric values. Consider a list of four integers:

// Output: 55
List.Average( { 22, 44, 66, 88 } )

Here, the function computes the average by summing all elements (22 + 44 + 66 + 88 = 220) and then dividing by the number of elements in the list (220 / 4 = 55).

Adding a Precision Type

Once you start working with detailled values, you may encounter interesting behavior. Power Query uses a double point precision by default. You can explicitly specify this precision in the second argument of List.Average by providing the Precision.Double enumeration.

// Output: 0,22222203333333335
List.Average( { 0.333333, 0.222222, 0.1111111 } )

List.Average( { 0.333333, 0.222222, 0.1111111 }, Precision.Double )

Both of these expressions return the same result. Now let’s see what happens when we swap the Precision.Type enumeration to Precision.Decimal:

// Output: 0,2222220333333333333333333333
List.Average( { 0.333333, 0.222222, 0.1111111 }, Precision.Decimal)

Notice how all of a sudden the outcome for our expressions has a lot more detail? This is due to the level of detail used by the two different precision values. When you need more detail, use Precision.Decimal.

Averaging Date Values

While you may expect number values as input, the List.Average function also accepts other values. For instance, to return the average of a range of dates you can use:

// Output: #date( 2016, 12, 31 )
List.Average ( 
  { 
    #date ( 2014, 12, 31 ), 
    #date ( 2016, 12, 31 ), 
    #date ( 2018, 12, 31 ) 
  } 
)

Averaging Time Values

Next, List.Average can compute the average of time values, giving a simple way to find the average of times provided. For providing time values you can use the #time function:

// Output: // #time( 6, 40, 0 )
List.Average( { #time( 12, 30, 0), #time( 0, 50, 0) } )

In this case, the function calculates the average by considering the internal numeric representation of the dates, effectively finding the average date value which is December 31, 2016.

Averaging DateTime Values

Similarly, when dealing with datetime values, List.Average computes the average point in time across the specified datetimes. You can use the #datetime function specify your values as shown here:

// Output: #datetime( 2016, 12, 30, 19, 20, 0 )
List.Average (
  {
    #datetime ( 2014, 12, 31, 5, 0, 0 ), 
    #datetime ( 2016, 12, 31, 5, 0, 0 ), 
    #datetime ( 2018, 12, 31, 0, 0, 0 )
  }
)

This result represents the midpoint in time among the provided datetime values, calculated by averaging the internal numeric representations of these datetimes.

Averaging DateTimeZone Values

This even works for #datetimezone values:

When applying the List.Average function to datetimezone values, specified using the #datetimezone function), it also accurately calculates the average. The calculating takes into account both the date and the time components along with the timezone offsets:

// Output: #datetimezone ( 2016, 10, 30, 21, 18, 20, 10, 0 ), 
List.Average (
  {
    #datetimezone ( 2014, 12, 31, 5, 0, 0, 10, 0 ), 
    #datetimezone ( 2016, 12, 31, 5, 0, 0, 9, 5 ), 
    #datetimezone ( 2018, 12, 31, 0, 0, 0, 5, 0 )
  }
)

Averaging Duration Values

Finally, duration values are also supported by List.Average. It computes the average duration, which might be useful in scenarios like averaging lengths of time intervals. Make use of the #duration function to specify these values:

// Output: #duration( 3, 13, 0, 0 )
List.Average( 
  { 
    #duration ( 5, 0 , 0 , 0 ), 
    #duration ( 0, 50, 0 , 0 ), 
    #duration ( 0, 0 , 30, 0 ) 
  } 
)

This result indicates the average duration computed across the different components (days, hours, minutes, seconds) of the provided durations.

Learn more about List.Average in the following articles:

Power Query Precision: Avoid Rounding Errors
Learn how to handle precision differences in Power Query. With these tips you can return accurate numbers and prevent rounding errors. » Read more

Other functions related to List.Average are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-average