List.Percentile

List.Percentile is a Power Query M function that calculates one or more sample percentiles of a list based on the specified percentiles and interpolation method. The function returns a single value or a list of percentiles corresponding to the input probability.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

List.Percentile(
   list as list,
   percentiles as any,
   optional options as nullable record,
) as any

Argument	Attribute	Description
list		A list of numerical values.
percentiles		A percentile or a list of percentiles (values between 0.0 and 1.0)
options	optional	The options argument supports a PercentileMode.Type, which specifies the percentile calculation method, defaulting to PercentileMode.ExcelInc when omitted. This uses an interpolation method compatible with Excel’s PERCENTILE.INC. Other available choices are: – PercentileMode.ExcelExc: Adopts interpolation similar to Excel’s PERCENTILE.EXC. – PercentileMode.SqlDisc: Follows SQL Server’s PERCENTILE_DISC for discrete percentile calculation. – PercentileMode.SqlCont: Uses a continuous interpolation method akin to SQL Server’s PERCENTILE_CONT.

Description

The List.Percentile function calculates sample percentiles from a given list of numbers. It takes two primary inputs: a list of numerical values and a percentile or a list of percentiles (values between 0.0 and 1.0).

• Single Percentile: If the second input is a single number within the 0.0 to 1.0 range, the function returns a single value. This value represents the data point in the list corresponding to that percentile probability.
• Multiple Percentiles: If the second input is a list of numbers, each within the 0.0 to 1.0 range, the function outputs a list of values. Each value in this list corresponds to the respective percentile probability in the input list.

Examples

Let’s have a look at an example.

Finding the Median

Suppose you have the list {2, 6, 0, 4, 9 } and want to find the list’s median (or the 50th percentile). Here’s the expression you can use:

// Output: 4
List.Percentile( {2, 6, 0, 4, 9}, 0.5 )

In this context 0.5 represents the 50th percentile indicating the midpoint of the data set when it is sorted. So, if you sort the list {2, 6, 0, 4, 9} in ascending order, it becomes {0, 2, 4, 6, 9}. The middle number in this sorted list is 4, which is why the function returns 4 as the result. This means that 50% of the numbers in the list are below or equal to 4.

Calculating Quartiles

In another example, we can look at the quartiles of a list. Using Excel’s PERCENTILE.EXC (represented by the enumeration PercentileMode.ExcelExc), we can calculate the 25th, 50th, and 75th percentiles of the list.

// Output: { 1, 4, 7.5 }
List.Percentile( 
  {2, 6, 0, 4, 9}, 
  { 0.25, 0.5, 0.75 }, 
  [PercentileMode=PercentileMode.ExcelExc] 
)

Here’s what each part of the expression does:

1. List {2, 6, 0, 4, 9}: This is the data set from which the percentiles are calculated.
2. Percentiles {0.25, 0.5, 0.75}: These numbers represent the 25th, 50th (median), and 75th percentiles. The function will calculate these specific percentiles from the given list.
3. PercentileMode.ExcelExc: This specifies the interpolation method to be used. Excel’s PERCENTILE.EXC calculates percentiles excluding the 0th and 100th percentiles, slightly differing from the more common inclusive methods.

The output {1, 4, 7.5} is interpreted as follows:

• 1: This is the 25th percentile, meaning that 25% of the data in the sorted list is less than or equal to 1.
• 4: This is the 50th percentile (median), indicating that half of the data is less than or equal to 4.
• 7.5: This is the 75th percentile, suggesting that 75% of the data is less than or equal to 7.5.

This output is the result of applying the ExcelExc percentile calculation method to the provided list. The method slightly alters the calculation of percentiles compared to other methods, leading to these specific values.

Related functions

Other functions related to List.Percentile are:

• List.Average
• List.Count
• List.Covariance
• List.Median
• List.Mode
• List.Modes
• List.NonNullCount
• List.Product
• List.StandardDeviation
• List.Sum

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-percentile