List.Percentile

D

Back to top

T

Back to top

#

Back to top

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

Understanding Percentiles

Before we dive into the M code behind the List.Percentile function, let’s first understand what percentiles are. A percentile is a measure used in statistics to indicate the value below which a given percentage of observations in a group of observations fall. For example, the 90th percentile is the value below which 90% of the observations fall.

Syntax of List.Percentile function

The syntax for the List.Percentile function is as follows:

List.Percentile(list as list, percentile as number, optional precision as nullable number) as any

The List.Percentile function takes in three arguments:

1. `list`: This is the list of values for which we want to calculate the percentile.

2. `percentile`: This is the percentile value that we want to calculate. It should be between 0 and 1.

3. `precision`: This is an optional argument that allows us to specify the number of decimal places to round the result to.

M Code Behind the List.Percentile Function

The List.Percentile function is implemented in M code, which is the language used by Power Query. Let's take a look at the M code behind the List.Percentile function:

let

sortedList = List.Sort(list),

index = percentile List.Count(sortedList) – 1,

fraction = Number.Mod(index, 1),

wholeNumber = Number.RoundDown(index),

lowerValue = List.Position(sortedList, wholeNumber),

upperValue = if lowerValue + 1 < List.Count(sortedList) then List.Position(sortedList, lowerValue + 1) else lowerValue,

result = lowerValue + fraction (List.Position(sortedList, upperValue) – lowerValue)

in

result

Let's break down the M code step by step:

1. The `List.Sort` function is used to sort the input list in ascending order.

2. The `index` variable is calculated as the product of the percentile value and the count of the sorted list minus 1.

3. The `fraction` variable is calculated as the remainder when the `index` variable is divided by 1.

4. The `wholeNumber` variable is calculated as the largest integer less than or equal to the `index` variable.

5. The `lowerValue` variable is calculated as the value in the sorted list at the index given by `wholeNumber`.

6. The `upperValue` variable is calculated as the value in the sorted list at the index given by `lowerValue + 1`, unless `lowerValue + 1` is out of range, in which case `upperValue` is set to `lowerValue`.

7. The `result` variable is calculated as the `lowerValue` plus the `fraction` times the difference between the `upperValue` and the `lowerValue`.

Examples

Let's take a look at some examples to understand how the List.Percentile function works:

Example 1

Suppose we have a list of values:

{100, 200, 300, 400, 500}

We want to calculate the 90th percentile of this list. Using the List.Percentile function, we can write the following formula:

List.Percentile({100, 200, 300, 400, 500}, 0.9)

The output of this formula is `450`, which is the 90th percentile of the list.

Example 2

Suppose we have a list of values:

{10, 20, 30, 40, 50}

We want to calculate the 50th percentile of this list. Using the List.Percentile function, we can write the following formula:

List.Percentile({10, 20, 30, 40, 50}, 0.5)

The output of this formula is `30`, which is the median of the list.

The List.Percentile function is a powerful tool within Power Query that allows us to calculate percentiles of a list of values. By understanding the M code behind this function, we can gain a deeper understanding of how it works and how we can use it to solve data transformation problems in Excel and Power BI.

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)