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.
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).
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:
For the record
Pass in the query text as the first parameter and you’ll get a text string for the record:
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:
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 😉