List.Median

D

Back to top

T

Back to top

#

Back to top

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

The M code behind the List.Median function is relatively simple and easy to understand. Here, we’ll take a closer look at how the function works and the M code that powers it.

Understanding the Median Function

Before we delve into the M code, let’s first review what the median is and how it works. The median is the middle value in a list of numbers. To calculate the median, we first need to sort the list in ascending order. If the list has an odd number of values, the median is the middle value. If the list has an even number of values, the median is the average of the two middle values.

For example, consider the list of numbers [5, 2, 8, 1, 6]. To calculate the median, we first sort the list in ascending order: [1, 2, 5, 6, 8]. The list has an odd number of values, so the median is the middle value, which is 5.

Now, let’s see how this process is performed using the List.Median function in Power Query.

The M code Behind List.Median

The List.Median function takes a list of numbers as its argument and returns the median value. The M code behind the function is as follows:

(List as list) =>

let

count = List.Count(List),

mid = Number.RoundUp(count / 2),

sorted = List.Sort(List),

median = if count mod 2 = 0 then

(sorted{mid} + sorted{mid + 1}) / 2

else

sorted{mid}

in

median

Let's break down this code into its individual parts.

The first line defines the function and its argument, which is a list of numbers. The second line calculates the number of values in the list using the List.Count function and assigns it to the variable "count".

The third line calculates the midpoint of the list using the Number.RoundUp function. If the list has an odd number of values, this will be the index of the middle value. If the list has an even number of values, this will be the index of the lower middle value.

The fourth line sorts the list in ascending order using the List.Sort function and assigns it to the variable "sorted".

The fifth line calculates the median value using an if statement. If the list has an even number of values (i.e., the count modulo 2 equals 0), the median is the average of the middle two values in the sorted list. If the list has an odd number of values, the median is the middle value in the sorted list.

Finally, the median value is returned as the result of the function.

Using List.Median

Now that we understand the M code behind the List.Median function, let's see how it's used in Power Query. Suppose we have a table of sales data with a column named "Revenue". We want to calculate the median revenue for all sales.

To do this, we'll create a new column using the following steps:

1. Select the "Revenue" column.

2. From the "Add Column" tab, select "Custom Column".

3. In the "Custom Column" dialog box, enter "Median" as the column name and the following formula as the custom formula:

List.Median([Revenue])

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

The List.Median function will be applied to each row in the "Revenue" column, and the median value will be returned for each row in the new "Median" column.

The List.Median function in Power Query is a useful tool for calculating the median value of a list of numbers. The M code behind the function is relatively simple and easy to understand, making it a good example for those learning to write M code in Power Query. By using this function, users can quickly and easily calculate median values in their data and gain valuable insights into their data sets.

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)