List.Modes - List Function

List.Modes is a Power Query M function that returns the item that appears most frequently in a list. The function returns all items if multiple items appear with the same maximum frequency or throws an exception if the list is empty.

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

Syntax

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

Argument	Attribute	Description
list		The list of values to return the most frequent appearing item from.
equationCriteria	optional	Uses Comparer Functions to determine how values are equated 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.Modes function identifies the items that appear most frequently in a given list. If the list is empty, it will result in an error. When multiple items share the highest frequency, all of them are returned. The output of the function is the unique list of items that appear most frequent in the list. You can also use an optional parameter, equationCriteria, to define custom rules for comparing items.

Examples

When you use the List.Modes function with a list of values, it finds all items that appear with the highest frequency. Here are some examples to illustrate how it works:

Single Most Frequent Value

When you provide the List.Modes function with a list of values, it will look for those that appear most frequently. For instance, inspect the following example:

List.Modes({1, 2, 3, 4, 4, 5}) // Output: {4}

In this list, each item appears once except for the number 4, which appears twice. Therefore, the function returns a list containing 4.

Multiple Values with the Same Frequency

If there are multiple values with the highest frequency, the function returns all of them.

List.Modes({1, 2, 2, 3, 4, 4, 5}) // Output: {2, 4}

In this example, both 2 and 4 appear twice. The function returns a list containing both 2 and 4.

Mixed Values

The function also works with lists containing mixed types of values.

List.Modes({"a", 1, "b", "b", 2, 2, #date(2023,1,1)}) // Output: {"b", 2}

In this case, both the letter “b” and the number 2 are returned, since both appear twice.

Learn more about List.Modes 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.Modes are:

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