List.Median - List Function

List.Median is a Power Query M function that calculates the median item of a list. The function returns the median item or null if the list contains no non-null values, and calculates the average of two middle items for even-sized numeric or date-based lists.

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

Syntax

List.Median(
   list as list,
   optional comparisonCriteria as any,
) as any

Argument	Attribute	Description
list		The list with values for computing the median.
comparisonCriteria	optional	Uses Comparer Functions to determine how values are compared during operations. Options include Comparer.Ordinal for exact case-sensitive matching, Comparer.OrdinalIgnoreCase for case-insensitive matching, and Comparer.FromCulture for culture-specific comparisons.

Description

The List.Median function is designed to find the median, or the middle value, of a given list. This function follows a series of steps to determine the median value. Below is a detailed explanation of how the function operates:

Removal of Null Values: Initially, the function eliminates any null values present in the list. This ensures that these nulls do not affect the calculation of the median.
Sorting the List: After removing null values, the function sorts the remaining elements. Sorting is crucial as the median is essentially the middle value of an ordered list.
Determining the Median:
- Odd Number of Values: If the list has an odd number of elements, the function simply returns the middle element of the sorted list.
- Even Number of Values: For lists with an even number of elements, the function typically returns the smaller of the two middle values. However, there is an exception when the list entirely consists of values of of the same type (datetime, duration, number, or time). In these cases, the function calculates the average of the two middle values and returns this average as the median.

You can provide an optional comparisonCriteria that allows you to specify how to compare values. Supported values are Comparer.Ordinal, Comparer.OrdinalIgnoreCase and Comparer.FromCulture.

Examples

Let’s look at some examples to learn how List.Median works.

Median of an Odd Number of Values

Let’s start with some examples to understand the median calculation for an odd number of items. When you have a list of values { 10, 20, 30, 40, 50 }, you can find the median value as follows:

List.Median( { 10, 20, 30, 40, 50 } ) // Output: 30

In this case, 30 is directly returned as the median since it is the middle item in the sorted list. The function sorts the list first, irrespective of the initial order of items:

List.Median( { 30, 10, 20, 40, 50 } ) // Output: 30

In the above sequence, you can find the value 30 is at start of the list, yet the output still returns 30. That shows values are sorted first, before returning the median.

Comparison with List.Average

Note that we could have returned the same value using the List.Average. You can see how List.Median is different from List.Average by looking at the following expression:

List.Median( { 10, 20, 35, 40, 50 } ) // Output 35

Here, while the median is 35, using List.Average on the same list would yield 31. The average is influenced by all values, whereas the median focuses solely on the middle.

Median of an Even Number of Values

When the list has an even amount of numbers in the list, List.Median sorts the list and returns the average between the two middle values.

// Returns the average of the number 35 and 37. Output: 36
List.Median( { 10, 20, 35, 37, 40, 50 } )

This output (36) is the average of the two middle numbers, 35 and 37. It’s important to note that averaging the medians occurs only when all items in the list are datetimes, durations, numbers, or times. Otherwise, the function would return the smaller of the two median values.

Using comparisonCriteria

There may also be cases where you want to control the way List.Median sorts values. For instance, the following two expressions sort values using ordinal rules:

// Output: "a"
List.Median( { "a", "A", "z", "b", "D" }  ) 
List.Median( { "a", "A", "z", "b", "D" }, Comparer.Ordinal )

Both expressions first sort the values in the order { "A", "D", "a", "b", "z" } and then find the median value, which is "a".

To ignore capitalization when sorting, you can provide a different comparer function, which is Comparer.OrdinalIgnoreCase:

List.Median( { "a", "A", "z", "b", "D" } , Comparer.OrdinalIgnoreCase  ) // Output "b"

In this case, values are sorted as { "a", "A", "b", "D", "z" }, where the letter "b" is sorted as the middle value. To provide culture specific comparison, you can make use of the comparer function Comparer.FromCulture. More examples are provided in that article.

Learn more about List.Median in the following articles:

Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more

Other functions related to List.Median are:

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