List.Mode - List Function

List.Mode is a Power Query M function that returns the item that appears most frequently in a list. The function returns the last item 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.Mode(
   list as list,
   optional equationCriteria as any,
) as any

Argument	Attribute	Description
list		The list of values to extract the most frequent value 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.Mode function finds the item that shows up the most in a list. If the list is empty, it throws an error. If several items appear with the same frequency, it picks the highest one. You can also use an optional parameter called equationCriteria to set your own rules for comparing items.

Examples

When you use the List.Mode function with a list of values, it finds the one that appears most frequently. Here are some examples to illustrate how it works:

Single Most Frequent Value

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

List.Mode( { 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 4.

Multiple Values with the Same Frequency

If there are multiple values with the highest frequency, the function returns the last one encountered in the list.

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

In this example, both 2 and 4 appear twice, but 4 is returned because it is the last one in the list with the highest frequency.

Mixed Values

The earlier two examples show a list of values. The function also works with lists containing mixed types of values.

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

In this case, the letter “b” is the only item that appears twice, so the function returns “b”.

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

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