List.Max

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the List.Max function, and how you can use it to manipulate data in Power Query.

Understanding the List.Max Function

The List.Max function is used to find the maximum value in a list of numbers. The syntax for the function is as follows:

List.Max(list as list) as any

The function takes a list of numbers as its argument, and returns the maximum value in that list. If the list is empty, the function returns null.

The M Code Behind List.Max

The M code behind the List.Max function is relatively simple. Here is the M code for the function:

let

List.Max = (list as list) =>

List.MaxOrNull(list),

List.MaxOrNull = (list as list) =>

List.Sort(list, Order.Descending){0}

in

List.Max

The code consists of two functions: List.Max and List.MaxOrNull.

The List.Max function calls the List.MaxOrNull function to find the maximum value in the list. If the list is empty, List.Max returns null.

The List.MaxOrNull function sorts the list in descending order, and returns the first value in the sorted list. This value is the maximum value in the original list.

Using List.Max in Power Query

Now that we understand the M code behind the List.Max function, let's see how we can use it in Power Query.

Suppose we have a table of sales data, and we want to find the maximum sales amount for each region. We can use the List.Max function to accomplish this task.

First, we need to load our sales data into Power Query. Once our data is loaded, we can use the Group By function to group our data by region, and then use the List.Max function to find the maximum sales amount for each region.

Here is the M code for this query:

let

Source = Excel.CurrentWorkbook(){[Name=”SalesData”]}[Content],

#”Changed Type” = Table.TransformColumnTypes(Source,{{“Region”, type text}, {“Sales”, Currency.Type}}),

#”Grouped Rows” = Table.Group(#”Changed Type”, {“Region”}, {{“MaxSales”, each List.Max([Sales]), Currency.Type}})

in

#”Grouped Rows”

The query first loads our sales data into Power Query, and then changes the data types of our columns to text and currency. We then use the Group By function to group our data by region, and find the maximum sales amount for each region using the List.Max function.

The resulting table will have two columns: Region and MaxSales. The MaxSales column will contain the maximum sales amount for each region.

In this article, we explored the M code behind the List.Max function in Power Query. We saw how this function is used to find the maximum value in a list of numbers, and how we can use it to manipulate data in Power Query.

By understanding the M code behind the List.Max function, we can more effectively use this function in our Power Query queries, and gain deeper insights into our data.

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)