TRIMMEAN is a statistical function in Excel that calculates the “mean taken by excluding a percentage of data points from the top and bottom tails of a data set”. So you can use it if you want to exclude potential outliers from your data. Daniil Maslyuk has a nice approach for it in DAX, but in here I want to share my M version for Power Query for it.
TRIMMEAN function for Power Query
https://gist.github.com/ImkeF/3eb65e732ffa0e8b6390c2f316dddbe5
Usage
It has the same function parameters than the Excel-function:
TRIMMEAN(array, percent)
The TRIMMEAN function syntax has the following arguments:
- Array Required. The array or range of values to trim and average.
-
Percent Required. The fractional number of data points to exclude from the calculation. For example, if percent = 0.2, 4 points are trimmed from a data set of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set.
If you are interested in more Power Query functions that replicate Excel functions who haven’t made it into the M-language (yet?) please check out this collection.
Enjoy and stay queryious 😉
Need help. What is the format for the array?