# 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) Power Query and M Intensive Training Course £1,050.00 – £26,550.00 Select optionsContinue Loading Done Power Query and M Introduction £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Intermediate £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Advanced £395.00 – £9,750.00 Select optionsContinue Loading Done Upcoming Courses Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```