Skip to content

Instantly share code, notes, and snippets.

@ImkeF
Created May 17, 2018 18:06
Show Gist options
  • Save ImkeF/287b48232079e5f69bc1e72019bd6b71 to your computer and use it in GitHub Desktop.
Save ImkeF/287b48232079e5f69bc1e72019bd6b71 to your computer and use it in GitHub Desktop.
let func =
(QueryText as text, optional HtmlAdress as text) =>
let
/* Debug parameters
QueryText = "// Source: https://gist.githubusercontent.com/tonmcg/c5889375a84482f2d2862d620b6f191d/raw/e576da943b79b4690c9b81494365d1f59b075b37/List.Percentile.pq #(lf) // from NIST 7.2.6.2 Percentiles#(lf)// https://www.itl.nist.gov/div898/handbook/prc/section2/prc262.htm#(lf)// Note that there are other ways of calculating percentiles in common use#(lf)// Hyndman and Fan (1996) in an American Statistician article evaluated nine different methods (R1 to R9)#(lf)// for computing percentiles relative to six desirable properties. #(lf)// Their goal was to advocate a ""standard"" definition for percentiles that would be implemented in statistical software. #(lf)// Although this has not in fact happened, most statistical and spreadsheet software use one of the methods described in Hyndman and Fan.#(lf)// The method used here is patterned after the R6, R7, and R8 methods; R7 is the default method used in Excel and R and thus the default for this function#(lf)let #(lf) List.Percentile = (sourceList as list, p as number, optional method as number) as number =>#(lf) let#(lf) method = if method is null then 7 else method,#(lf) N = List.Count(sourceList),#(lf) list = List.Sort(sourceList,Order.Ascending),#(lf) pth = #(lf) if method = 6 then #(lf) p * (N + 1) #(lf) else if method = 7 then #(lf) 1 + p * (N - 1) #(lf) else#(lf) if p < (2/3)/(N + (1/3)) or p = (2/3)/(N + (1/3)) then #(lf) list{0} #(lf) else if p > (N - (1/3))/(N + (1/3)) or p = (N - (1/3))/(N + (1/3)) then #(lf) list{N}#(lf) else#(lf) p * (N + (1/3) + (1/3)),#(lf) k = Number.IntegerDivide(pth,1),#(lf) d = Number.Mod(pth,1),#(lf) Yp = if k > 0 and k < N then list{k-1} + d * (list{k} - list{k-1}) else if k = 0 then list{0} else if k > N or k = N then list{N} else null#(lf) in#(lf) Yp,#(lf) DefineDocs = [#(lf) Documentation.Name = "" List.Percentile"",#(lf) Documentation.Description = "" Estimate a proportion of the data that falls above and below a given value."",#(lf) Documentation.LongDescription = "" Estimate a proportion of data above and below a percentage. The sourceList is the source list for the method. The percentile, p, denotes a value, such that at most (100 * p)% of the measurements are less than this value and at most 100(1 − p)% are greater. The optional parameter, method, is an integer between 1 and 9 that selects one of the nine quantile algorithms detailed in Hyndman and Fan (1996). Note: only methods 6, 7, and 8 are initialized currently."",#(lf) Documentation.Category = "" List.Ordering"",#(lf) Documentation.Source = "" Default is patterned after R and Excel's R7 method of calculating percentiles"",#(lf) Documentation.Author = "" Tony McGovern: www.emdata.ai"",#(lf) Documentation.Examples = {#(lf) [#(lf) Description = ""Calculate the 50th percentile (50%) value from an ordered list of values."", #(lf) Code = "" Percentile({95.1772,95.1567,95.1937,95.1959,95.1442,95.061,95.1591,95.1195,95.1065,95.0925,95.199,95.1682}, 0.5)"", #(lf) Result = ""95.1579""#(lf) ]#(lf) }#(lf) ] #(lf)in #(lf) Value.ReplaceType(#(lf) List.Percentile, #(lf) Value.ReplaceMetadata(#(lf) Value.Type(List.Percentile), #(lf) DefineDocs#(lf) )#(lf) )",
HtmlAdress = "https://goo.gl/THkj1A",
End of debug parameters */
GetAllFunctionsFromShared = #shared,
ConvertToTable = Record.ToTable(GetAllFunctionsFromShared),
CheckIfFunctionIsIncludedInSource = Table.AddColumn(ConvertToTable, "FunctionIncluded", each Text.Contains(QueryText, [Name])),
FilterOnlyIncluded = Table.SelectRows(CheckIfFunctionIsIncludedInSource, each ([FunctionIncluded] = true))[Name],
CreateStringPerFunction = List.Transform(FilterOnlyIncluded, each "#""" & _ & """ = " & _),
CombineAndFormat = "[ #(lf)" & Text.Combine(CreateStringPerFunction, ", #(lf)") & "#(lf) ]",
Result = if HtmlAdress = null then CombineAndFormat else "Expression.Evaluate(
Text.FromBinary(Web.Contents(""" & HtmlAdress & """)), #(lf)" & CombineAndFormat & "#(lf) )"
in
Result ,
documentation = [
Documentation.Name = " Text.FunctionRecordExpressionEvaluate ",
Documentation.Description = " Creates a string for a record or function (if 2nd parameter is used) for the function record in Expression.Evaluate, replacing #shared. ",
Documentation.LongDescription = " Creates a string for a record or function (if 2nd parameter is used) for the function record in Expression.Evaluate, replacing #shared. ",
Documentation.Category = " Text ",
Documentation.Source = " . https://wp.me/p6lgsG-QN . ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . ",
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