List.Average Function in Power Query M (5 Easy Examples)

List.Average returns the average (the mean) of the numbers in a list. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

If you want the average of a set of values, or the mean of a whole table column, List.Average is what you use.

Table of Contents

Syntax of List.Average Function

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

where

list (required, list). The list of values to average. It can hold numbers, dates, times, datetimes, datetimezones, or durations.
precision (optional, nullable number). Controls the precision of the division, either Precision.Double (the default) or Precision.Decimal for more accurate decimal math. Omit it for the default.

Returns: the average of the list, in the same datatype as the values it averaged. An empty list, or a list of all null, returns null.

In plain terms, you hand it a list of numbers and it gives you back their average.

Example 1: Average a simple list of numbers

Average four numbers passed straight into the function.

let
    Source = List.Average({10, 20, 30, 40})
in
    Source

Result: 25

The four values add up to 100, divided by 4 gives 25.

Example 2: Average a table column

Averaging a column is the most common real use of List.Average.

Say you have a ResponseTimes query with an Endpoint and a ResponseMs column.

You want the average response time across all endpoints.

Here is the starting data:

Endpoint	ResponseMs
Login	120
Search	95
Checkout	140
Profile	110
Reports	135

Pull the column out with Table.Column, then average it:

let
    Source = Excel.CurrentWorkbook(){[Name="ResponseTimes"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"ResponseMs", type number}}),
    Result = List.Average(Table.Column(Typed, "ResponseMs"))
in
    Result

Result: 120

The Table.TransformColumnTypes step ascribes type number first, because a loaded column comes in as type any and averaging that can error or return null.

Example 3: Null values are ignored

List.Average skips null items instead of counting them as zero.

let
    Source = List.Average({85, 90, null, 95})
in
    Source

Result: 90

Only the three real numbers are averaged. 270 divided by 3 is 90, not 270 divided by 4.

Example 4: An empty list returns null

If there is nothing to average, the result is null.

let
    Source = List.Average({})
in
    Source

Result: null

There are no values to divide, so you get null back. A list made up entirely of null behaves the same way.

Example 5: Round the average with Number.Round

The raw average of decimals can run to many places, so wrap it in Number.Round to control how many you keep.

let
    Source = Number.Round(List.Average({81.2, 76.6, 90.4, 88.8}), 2)
in
    Source

Result: 84.25

The four scores average to 84.25, and Number.Round(...,2) keeps it at two decimal places.

Things to keep in mind with List.Average

A loaded column is type any, so ascribe type number first. Averaging an untyped column can throw or return null. Set the type with Table.TransformColumnTypes or Value.ReplaceType before you call List.Average (Example 2).
The result keeps the list’s datatype. Average a list of dates and you get a date, average durations and you get a duration. It is not always a plain number.
There is no built-in trimmed or weighted average. For a weighted mean, divide List.Sum of the products by List.Sum of the weights, building the products with List.Transform or List.Accumulate. For a trimmed mean, sort and remove the outliers first, then average what is left.
precision only affects the division, not rounding. To control the number of decimal places shown, wrap the call in Number.Round (Example 5). Precision.Decimal just makes the decimal math more exact.

Common questions about List.Average

Should I use List.Average, or List.Sum divided by List.Count?

List.Average is the direct way and it handles null for you. List.Sum(list)/List.Count(list) divides by the full count including null items, so the two can disagree on a list with blanks.

How do I average a column while ignoring blank cells?

You already are. List.Average ignores null values automatically (Example 3), so a column with empty cells averages only the filled ones with no extra work.

List of All Power Query Functions

Related Power Query Functions / Articles: