List.Percentile

This function returns one or more sample percentiles of the provided list. If the value for percentiles falls between 0.0 and 1.0, it will be interpreted as a percentile, resulting in a single value corresponding to that probability. When the percentiles value is a list of numbers ranging from 0.0 to 1.0, the output will be a list of percentiles corresponding to the input probabilities. Advanced users can utilize the PercentileMode option in the settings to select a more specific interpolation method, although this is not recommended for most users. The predefined symbols PercentileMode.ExcelInc and PercentileMode.ExcelExc align with the interpolation methods used in Excel functions PERCENTILE.INC and PERCENTILE.EXC, respectively. The default behavior aligns with PercentileMode.ExcelInc. Additionally, symbols PercentileMode.SqlCont and PercentileMode.SqlDisc correspond with the SQL Server behaviors for PERCENTILE_CONT and PERCENTILE_DISC, respectively.

Determine the first quartile of the set {5, 3, 1, 7, 9} using the Power Query M function. Power Query M provides the function List.Percentile to calculate the specified percentile of a given list of numbers. In this case, the first quartile (25th percentile) of the list {5, 3, 1, 7, 9} is 3. To find the quartiles of the list {5, 3, 1, 7, 9} using an interpolation method that matches Excel's PERCENTILE.EXC function, follow these steps: 1. Utilize the Power Query M function List.Percentile. 2. Input the list of numbers {5, 3, 1, 7, 9} and specify the desired percentiles as {0.25, 0.5, 0.75}. 3. Set the PercentileMode parameter to PercentileMode.ExcelExc to match Excel's interpolation method. 4. The output will be {2, 5, 8}, representing the first, second, and third quartiles of the list.