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 anyDescription
Returns one or more sample percentiles of the list list. If the value percentiles is a number between 0.0 and 1.0, it will be treated as a percentile and the result will be
a single value corresponding to that probability. If the value percentiles is a list of numbers with values between 0.0 and 1.0, the result will be a list of percentiles
corresponding to the input probability. The PercentileMode option in options can be used by advanced users to pick a more-specific interpolation method but is not recommended for most uses.
Predefined symbols PercentileMode.ExcelInc and PercentileMode.ExcelExc match the interpolation methods used by the Excel functions
PERCENTILE.INC and PERCENTILE.EXC. The default behavior matches PercentileMode.ExcelInc. The symbols
PercentileMode.SqlCont and PercentileMode.SqlDisc match the SQL Server behavior for PERCENTILE_CONT and
PERCENTILE_DISC, respectively.
Examples
Find the first quartile of the list {5, 3, 1, 7, 9}.
// Output: 3
List.Percentile( {5, 3, 1, 7, 9}, 0.25 )Find the quartiles of the list {5, 3, 1, 7, 9} using an interpolation method matching Excel’s PERCENTILE.EXC.
// Output: {2, 5, 8}
List.Percentile( {5, 3, 1, 7, 9}, {0.25, 0.5, 0.75}, [PercentileMode=PercentileMode.ExcelExc] )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
