List.Percentile
ListReturns the value at the specified percentile(s) in the list. Percentile is expressed as a number from 0 to 1.
Syntax
List.Percentile(list as list, percentiles as any, optional options as nullable record) as anyParameters
| Name | Type | Required | Description |
|---|---|---|---|
list | list | Yes | The list of numeric values. |
percentiles | any | Yes | A number from 0 to 1 representing the percentile (e.g., 0.5 for median, 0.75 for 75th percentile), or a list of such numbers. |
options | nullable record | No | An optional record to configure interpolation method. Supports [PercentileMode = PercentileMode.ExclInclusive] or similar options. |
Return Value
any — The value at the given percentile, or a list of values if multiple percentiles are specified.
Remarks
List.Percentile computes the value at one or more percentiles within a numeric list. Percentiles are specified as values between 0 and 1 inclusive, where 0 returns the minimum and 1 returns the maximum. Passing 0.5 gives the median, equivalent to List.Median.
Pass a single number to get a single scalar result. Pass a list of numbers (e.g., {0.25, 0.5, 0.75}) to compute multiple percentiles in one call, which returns a list of results in the same order as the input percentiles.
Null values in the list are ignored. By default, linear interpolation is used between data points when the percentile falls between two values. The optional options record accepts a PercentileMode field to control the interpolation method (e.g., PercentileMode.ExclInclusive for Excel's PERCENTILE.EXC behavior, PercentileMode.InclInclusive for PERCENTILE.INC behavior). For statistical reporting, passing all four quartile percentiles at once is the most efficient approach.
Examples
Example 3: Multiple percentiles at once
List.Percentile({10, 20, 30, 40, 50, 60, 70, 80, 90, 100}, {0.25, 0.5, 0.75})Result | |
|---|---|
| 1 | {32.5, 55, 77.5} |
Example 4: Quartile analysis of sales data
let
Sales = {1200, 850, 3400, 450, 2100, 975, 1650, 2800},
Quartiles = List.Percentile(Sales, {0.25, 0.5, 0.75}),
Q1 = Quartiles{0},
Median = Quartiles{1},
Q3 = Quartiles{2}
in
#table({"Q1", "Median", "Q3"}, {{Q1, Median, Q3}})The final output — a single-row table showing Q1, Median, and Q3 for the sales data.
Q1 | Median | Q3 | |
|---|---|---|---|
| 1 | 962.50 | 1,425 | 2,275 |