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

If you want to find the value that shows up most often in a list, the List.Mode function is what you reach for. It scans the list and hands back the single most frequent item. Available in Excel (Power Query), Power BI Desktop, and Power BI Service.

Table of Contents

Syntax of List.Mode Function

List.Mode(list as list, optional equationCriteria as any) as any

where

list (required, list). The list of values to scan for the most frequent item.
equationCriteria (optional, any). Controls how two values are judged equal before they get counted, for example a Comparer function for case-insensitive matching. Omit it for the default exact match.

Returns: a single value, the item that appears most frequently in the list. If two or more values tie for most frequent, the last one in list order is returned. An empty list raises an error.

In plain terms, you give it a list and it tells you which value occurs the most.

Example 1: Find the most frequent number

Say you have a list of numbers and you want the one that appears most often.

let
    Source = List.Mode({3,7,7,2,7,9,2})
in
    Source

Result: 7

The value 7 shows up three times, more than any other number, so it is the mode.

Example 2: Find the most common text value

List.Mode works on text too, which is handy for finding the most common category or label.

let
    Source = List.Mode({"Apple","Banana","Apple","Cherry","Apple","Banana"})
in
    Source

Result: Apple

Apple appears three times against two for Banana and one for Cherry, so it comes back as the mode.

Example 3: On a tie, the last value wins

When two values share the highest count, List.Mode does not error or pick one at random. It keeps the tied value whose deciding occurrence comes last in the list.

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

Result: 20

Both 10 and 20 appear twice. The tie breaks in favor of 20 because its final occurrence sits later in the list than 10‘s.

Example 4: Get all tied values with List.Modes

When ties matter and you want every winner, use the sibling function List.Modes (plural). It returns all the most-frequent values as a list instead of just one, similar to how List.Transform hands back a list rather than a single value.

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

Result: {10, 20}

Same data as Example 3, but now both tied values come back together in a list, so you decide what to do with them.

Example 5: Find the mode of a table column

The most common real-world use is finding the most frequent value in a column, like the busiest region or the top-selling product.

Here is the starting data:

Region
East
West
East
North
East
West

Pull the Region column into List.Mode:

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    MostCommonRegion = List.Mode(Source[Region])
in
    MostCommonRegion

This counts each region and returns the one that appears most.

Result: East

East shows up three times against two for West and one for North, so it is the mode of the column.

Example 6: Count values as equal regardless of case

By default "red" and "RED" count as different values. Pass Comparer.OrdinalIgnoreCase as the equationCriteria to treat them as the same before counting. This is the same optional comparer that List.Contains accepts.

let
    Source = List.Mode({"red","RED","blue","Red","blue"},Comparer.OrdinalIgnoreCase)
in
    Source

Result: red

With case ignored, the three red variants count as one value with three hits, beating blue at two. The first-encountered spelling, red, comes back as the representative.

Things to keep in mind with List.Mode

An empty list throws an error. List.Mode({}) raises Expression.Error: There weren't enough elements in the enumeration to complete the operation. Guard a possibly empty list before you call it.
Use List.Modes when ties matter. List.Mode always returns one value and silently drops the other tied winners (Example 3). Reach for List.Modes to get the full list of them (Example 4).
Without equationCriteria, matching is exact. Text is case-sensitive and 1 does not equal "1", so near-duplicates count as separate values. Pass a Comparer to loosen this (Example 6).
A column is just a list. Source[ColumnName] gives you the column as a list, which is why List.Mode(Source[Region]) works directly on a table column (Example 5).
To count occurrences yourself, pair it with List.Count. List.Mode only names the winner. Use List.Count on a filtered list when you also need how many times it appeared.

Common questions about List.Mode

What is the difference between List.Mode and List.Modes?

List.Mode returns a single value and breaks ties by keeping the last one. List.Modes returns every tied value as a list, so use it whenever more than one value could share the top count.

Can List.Mode find the most frequent value across multiple columns?

Not directly, since it takes one list. Combine the columns into a single list first with List.Combine({Source[ColA],Source[ColB]}), then pass that to List.Mode.

List of All Power Query Functions

Related Power Query Functions / Articles: