Text.FunctionRecordExpressionEvaluate.pq

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