List.Mode

D

Back to top

T

Back to top

#

Back to top

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

What is the List.Mode Function?

The List.Mode function is used to find the most frequently occurring value in a list of values. It takes a list as its input and returns the most common value in that list. If there are multiple values that occur with the same frequency, List.Mode returns the first value that it encounters.

How Does List.Mode Work?

The List.Mode function works by first sorting the list in ascending order, then counting the number of occurrences of each value in the list. Once it has counted all of the occurrences, it returns the value with the highest count.

Here is an example of how the List.Mode function works:

Suppose we have the following list of values:

{1, 2, 3, 4, 3, 2, 1, 4, 5, 3}

The List.Mode function would first sort this list in ascending order:

{1, 1, 2, 2, 3, 3, 3, 4, 4, 5}

Next, it would count the number of occurrences of each value in the list:

{2, 2, 2, 3, 2}

Finally, it would return the value with the highest count, which is 3.

Understanding the M Code Behind List.Mode

The List.Mode function is written in the Power Query M language, which is a functional language that is used to define data transformations. Here is the M code behind the List.Mode function:

(list as list) =>

let

sortedList = List.Sort(list),

counts = List.CountOccurrences(sortedList),

maxCount = List.Max(counts),

maxIndex = List.PositionOf(counts, maxCount),

mode = List.Position(sortedList, maxIndex)

in

mode

Let's break down this code step by step:

1. `(list as list) =>`: This is the function definition. It takes a list as its input and returns a value.

2. `sortedList = List.Sort(list)`: This line sorts the input list in ascending order and assigns the result to the variable `sortedList`.

3. `counts = List.CountOccurrences(sortedList)`: This line counts the number of occurrences of each value in the sorted list and assigns the result to the variable `counts`.

4. `maxCount = List.Max(counts)`: This line finds the highest count in the `counts` list and assigns the result to the variable `maxCount`.

5. `maxIndex = List.PositionOf(counts, maxCount)`: This line finds the index of the highest count in the `counts` list and assigns the result to the variable `maxIndex`.

6. `mode = List.Position(sortedList, maxIndex)`: This line finds the value in the sorted list at the index specified by `maxIndex` and assigns the result to the variable `mode`.

7. `mode`: This line returns the value of `mode`.

Using List.Mode in Power Query

To use the List.Mode function in Power Query, you can simply call it and pass in a list as its argument. Here is an example of how to use List.Mode in a Power Query query:

1. Open a new Power Query query in Excel or Power BI.

2. Click on the "Add Column" tab and select "Custom Column".

3. In the "Custom Column" dialog box, enter a name for the new column (e.g. "Mode").

4. In the "Custom Column" dialog box, enter the following formula: `List.Mode([List])`, where `[List]` is the name of the column that contains the list of values.

5. Click "OK" to create the new column.

6. You should now see a new column in your query that contains the most frequently occurring value in the list.

The List.Mode function is a powerful tool for finding the most frequently occurring value in a list of values in Power Query. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it in your own projects. With the ability to perform complex data transformations and manipulations, Power Query is a valuable tool for any data analyst or business intelligence professional.

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)