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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| let func = | |
| // fnTRIMMEAN | |
| (array, percent) => | |
| let | |
| percentage = | |
| if percent > 1 or percent < 0 | |
| then error Error.Record("Percentage must be between 0 and 1 (100%)") | |
| else percent, | |
| Source = List.Buffer( List.Sort(array, Order.Ascending) ), | |
| CountOfTotalRows = List.Count( Source ), | |
| CutOffAtEachSide = Number.RoundDown((percentage * CountOfTotalRows) / 2), | |
| RelevantRange = List.Range( Source, CutOffAtEachSide, CountOfTotalRows – CutOffAtEachSide * 2), | |
| Result = List.Average( RelevantRange) | |
| in | |
| Result , | |
| documentation = [ | |
| Documentation.Name = " Xls.TRIMMEAN.pq ", | |
| Documentation.Description = " Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. ", | |
| Documentation.LongDescription = " Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set. https://support.microsoft.com/en-us/office/trimmean-function-d90c9878-a119-4746-88fa-63d988f511d3?ui=en-us&rs=en-us&ad=us ", | |
| Documentation.Category = " Xls.Statistical ", | |
| Documentation.Source = " www.TheBIccountant.com ", | |
| Documentation.Version = " 1.0 ", | |
| Documentation.Author = " Imke Feldmann ", | |
| Documentation.Examples = {[Description = " ", | |
| Code = " ", | |
| Result = " "]}] | |
| in | |
| Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation)) |
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 😉
Pingback: TIMMEAN() in Power Query – Curated SQL
Need help. What is the format for the array?