# TRIMMEAN function for Power Query and Power BI

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.

 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))

view raw

Xls.TRIMMEAN.pq

hosted with ❤ by GitHub

### 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 😉