Automatically create function record for Expression.Evaluate in Power BI and Power Query

Some time ago I wrote a blogpost on how to create a function library in Power BI or Power Query (http://www.thebiccountant.com/2017/08/27/how-to-create-and-use-r-function-library-in-power-bi/). There I also presented a way to pull that function code automatically from GitHub.

Problem

In that code I used the function Expression.Evaluate to execute the imported text and create functions from it. The inbuilt functions that I’ve used in that code have to be passed as an environment record at the end of the expression. I’ve used #shared for it, as this returns a record with all native M-functions and is quick and easy to write (if environments are new to you, check out this series: https://ssbi-blog.de/the-environment-concept-in-m-for-power-query-and-power-bi-desktop/ ). But as it turns out, this can cause problems when publishing to the service unfortunately (https://social.technet.microsoft.com/Forums/ie/en-US/208b9365-91e9-4802-b737-de00bf027e2a/alternative-calling-function-with-text-string?forum=powerquery – please leave a vote if you would like to use #shared in the service as well).

Solution

To bypass these problems, you can specify each used function individually (as you can see in Tony McGoverns newest treasure here for example: https://www.tonymcgovern.com/powerquery/calculate-percentile/ ).

But depending on the length and complexity of your function, determining which functions are used and manually write down the record can become a bit laborious and spoil the fun. Therefore, I’ve created a function that does that for you autoMagically:

The formula


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

view raw

Text.FunctionRecordExpressionEvaluate.pq

hosted with ❤ by GitHub

For the record

Pass in the query text as the first parameter and you’ll get a text string for the record:

Function record for Expression.Evaluate

For the query

And if you additionally pass in the URL for your function code as well, the full Expression.Evaluate-statement will automatically be created for you:

Full query text for Expression.Evaluate

Be aware, that the results of these functions are text that you have to copy and paste into your function code. You cannot simply reference the query, as this would repeat the problem with #shared.

Enjoy & stay queryious 😉

Comments (5) Write a comment

  1. Pingback: How to create and use an R-function-library in Power BI – The BIccountant

  2. Pingback: Using functions to create a filter in Power Query - Foster BI

  3. Pingback: Dynamically create types from text with Type.FromText in Power Query and Power BI

  4. Hello there,

    Is there a way of using the output directly and make it become a record? I am not using #shared at all. I have a list with all the names of my custom functions.

    Assuming my custom functions’ names are a, b and c. Your code helps generating the record codes [a=a,b=b,c=c] for the ease of copy-and-paste, but I want to make a actual record out of it.

    Is there a way of doing so?

    Thank you so much for your help.

    Best regards,
    David

    Reply

    • Hi David,
      no, the nature of this solution prohibits this (as it’s using shared itself and wouldn’t work in the service).
      So it’s just a helper-function that returns the string for your specific solution.
      /Imke

      Reply

Leave a Reply