Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Last active March 14, 2022 22:11
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ImkeF/3eb65e732ffa0e8b6390c2f316dddbe5 to your computer and use it in GitHub Desktop.
Save ImkeF/3eb65e732ffa0e8b6390c2f316dddbe5 to your computer and use it in GitHub Desktop.
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.
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))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment