List.Modes

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function List.Modes

In this article, we will take a deep dive into the M code behind one of the most commonly used M functions, List.Modes. We will explore its syntax, arguments, and usage scenarios.

Syntax of List.Modes

The List.Modes function in Power Query is used to return a list of the most frequently occurring values in a given list. The syntax of List.Modes is as follows:

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

The first argument, 'list', is a required argument that specifies the list of values for which we want to find the most frequent values. The second argument, 'optional equationCriteria', is an optional argument that specifies the criteria for comparing the values in the list.

Arguments of List.Modes

Let's explore the arguments of List.Modes in more detail.

Argument 1: list

The first argument of List.Modes is a required argument that specifies the list of values for which we want to find the most frequent values. The 'list' argument can be a column of a table, a list of values, or an expression that returns a list.

Argument 2: optional equationCriteria

The second argument of List.Modes is an optional argument that specifies the criteria for comparing the values in the list. The 'equationCriteria' argument can be any value that returns a true or false result when compared with the values in the list.

For example, suppose we have a list of numbers and we want to find the most frequent values in the list. We can use the following formula:

List.Modes({1, 2, 3, 4, 4, 5, 5, 5, 6})

This will return a list of the most frequent values in the list, which are 4 and 5.

Usage Scenarios of List.Modes

The List.Modes function can be used in a wide range of data transformation scenarios. Let's explore a few usage scenarios of List.Modes.

Scenario 1: Finding the most frequent values in a column

Suppose we have a table of sales data that contains a column of product names. We want to find the most frequently sold products in the table. We can use the following formula:

List.Modes(Sales[Product])

This will return a list of the most frequently sold products in the 'Product' column.

Scenario 2: Finding the most frequent values in a list

Suppose we have a list of employee names and we want to find the most common names in the list. We can use the following formula:

List.Modes({“John”, “Mary”, “Kate”, “John”, “John”, “Kate”})

This will return a list of the most common names in the list, which are John and Kate.

Scenario 3: Finding the most frequent values based on a criteria

Suppose we have a table of sales data that contains a column of product names and a column of sales regions. We want to find the most frequently sold products in each sales region. We can use the following formula:

Table.Group(Sales, {“Sales Region”}, {{“Most Frequent Products”, each List.Modes([Product])}})

This will return a table with the most frequently sold products in each sales region.

In conclusion, the List.Modes function is a powerful tool for finding the most frequent values in a list or a column of a table. Its simple syntax and flexible arguments make it a versatile tool for a wide range of data transformation scenarios. By understanding the M code behind List.Modes, you can take your Power Query skills to the next level and perform advanced data transformations with ease.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)