List.Median Function in Power Query M (6 Easy Examples)

If you want the middle value of a list of numbers, or the middle item of any sorted list, the List.Median function is what you reach for. It sorts the values and hands you back the one in the middle. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of List.Median Function

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

where

list (required, list). The list whose median you want. The values are sorted first, and any null values are dropped before the median is found.
comparisonCriteria (optional, any). Controls how items are ordered, for example Comparer.OrdinalIgnoreCase for case-insensitive ranking. Omit it for the default ordering.

Returns: the median item of the list, with a type that matches the items (a number for a numeric list, text for a text list). Returns null if the list has no non-null values.

In plain terms, you give it a list, and it sorts the values and returns the one sitting in the middle.

Example 1: Median of an odd-count number list

Find the middle value of five numbers.

let
    Source = List.Median({14,9,21,3,17})
in
    Source

Result: 14

Sorted, the list is {3,9,14,17,21}. There is an odd count, so the single middle value is 14.

Example 2: An even-count number list averages the two middles

With an even count there is no single middle value, so two values sit in the middle. For a numeric list, List.Median averages them.

let
    Source = List.Median({20,40,60,80})
in
    Source

Result: 50

The two middle values are 40 and 60. Their average is 50, exactly how the worksheet MEDIAN function behaves.

Example 3: The average can be a fraction

The averaged result is not rounded, so an even-count list can return a value that is not in the original list.

let
    Source = List.Median({6,10,15,19})
in
    Source

Result: 12.5

The two middle values are 10 and 15, and their average is 12.5.

Example 4: Median of a table column

List.Median takes a list, so to use it on a table you pass it a single column with Source[ColumnName].

Here is the starting data:

Student	Score
Aisha	88
Ben	72
Carlos	95
Dana	60
Evan	79

Pull the Score column into List.Median:

let
    Source = Excel.CurrentWorkbook(){[Name="Scores"]}[Content],
    Result = List.Median(Source[Score])
in
    Result

Result: 79

Sorted, the scores are {60,72,79,88,95}, so the middle score is 79.

Example 5: A text list returns the smaller middle, not an average

This is something the worksheet MEDIAN function cannot do at all. With an even-count text list, List.Median returns the smaller of the two middle items instead of averaging.

let
    Source = List.Median({"banana","apple","cherry","date"})
in
    Source

Result: banana

Sorted, the list is {"apple","banana","cherry","date"}. The two middle items are banana and cherry, and List.Median returns the smaller one, banana.

Example 6: An empty list returns null

Calling List.Median on a list with no values gives null rather than an error.

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

Result: null

There are no non-null values to rank, so the result is null. The same happens if every item in the list is null, since those are dropped first.

Things to keep in mind with List.Median

Even-count behavior depends on the item type. Numbers, dates, date-times, times, and durations average the two middle values (Examples 2 and 3). Every other type, including text, returns the smaller of the two instead (Example 5).
The result type matches the items, not always a number. A text list gives text back, so a later step should not assume it is dealing with a number.
comparisonCriteria changes which item is the middle. It controls the sort order, so on a text list Comparer.OrdinalIgnoreCase can rank items differently and change the median you get.
It differs from worksheet MEDIAN on text. Both average a numeric even-count list, but MEDIAN errors on text while List.Median returns a middle text value.

Common questions about List.Median

What is the difference between List.Median and List.Average?

List.Average always adds the values and divides by the count, so it works only on numbers. List.Median returns a middle item and works on text too, ignoring outliers that would skew an average.

Does List.Median ignore null values?

Yes. It drops every null before ranking, so they never count toward the middle position. A list of only null values returns null.

List of All Power Query Functions

Related Power Query Functions / Articles: