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


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 😉

Comment (1) Write a comment

  1. Pingback: TIMMEAN() in Power Query – Curated SQL

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz