How to create and use an R-function-library in Power BI

Edit 10-10-2017: There is also a (simpler) way to run a custom function library described here: http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/ . If you go that route, the only point of interest in the article might be how to create your function library automatically.

Once you’ve discovered the huge potential R gives you to expand your analytical toolbox in Power BI (check some tips & tricks in my previous blogpost if you haven’t already), you might wish to have all your awesome functions conveniently at hand when designing new solutions. And thanks to M, there’s actually nothing easier than that: R-function-library in a record (which works just the same for M-functions 🙂 )

Put your functions into a record (fnr) with the function name as the field name and the function itself as the value: One query to hold them all (and not cluttering your editor pane) and ready to use as if they were native functions:

R-function-library

Use

will export content of my query “Actuals” to csv-file on my desktop.

  1. fnr is the name of the record. You can give it your own name of course, I prefer to keep this as short as possible.
  2. followed in square bracket is the name of the function (record field name)
  3. in ordinary brackets you have the function arguments just like in standard M (record value)

Create record

The most primitive way is the manual method wich you can directly do in the advanced editor in Power BI or in another text-editor of your choice and then paste the result back to Power BI:

  1. Paste function code
  2. Add a name, followed by an equation-sign to it
  3. wrap all this into square brackets

With this code in PBI advanced editor, this record will be returned:

Now if you want to add another function:

  1. Copy function code and paste after the opening square bracket
  2. Add a comma to separate from existing code
  3. Jump back to the beginning and add function name with equation sign like before

Voila: 2 functions in the record:

Manage your record

Once your function-library-record grows nicely, you might appreciate a convenient method to search for the right function in it. Therefore I’ve created a function that displays the function metadata in table-form that you can easily search and filter:

So if you’re disciplined enough to add metadata to your functions, it will pay off also for nice search capabilities 🙂

Just reference the function-record as the parameter:

Subscribers can download this workbook where this is implemented already: RM_Library_Simple.zip

Create your function library automatically

Of course, M’s super powers lend themselves to automate this process 🙂

1 – Fetch code from GitHub

This function will fetch all functions from a GitHub-repository:

The function parameters for my repo are shown in row 6+7 (BTW: they are commented out & I often use this technique for being able to quickly debug functions)

But other repos might have a different syntax, so you need to check for each repo in GitHub individually!

Also the function filters on files with ending “m” or “rm”. So if you want to fetch different file endings as well, you have to adjust the code in row 10, as this is hardcoded & not part of the parameters.

2 – Transform to library-record

What’s cool here is that we can transform the table from above directly to a function-library-record without having to copy anything to a text-editor. Just feed the table you’ve created above as parameter to this function:

It uses Expression.Evaluate to transform the function code into an executable function returning the same function-library-record as the manual method from above.

3 – Export to csv and consume from there

As cool and magic this is, it takes a while to update and I just use it to collect the code and refresh once there are new functions. I then export the table to a local csv-file and use the code from there. This time as query, as this will directly become my library-function-record “fnr”:

Subscribers  can download the file here: RM_Library_GitHub.zip

and a simple version that “just” consumes the local csv-file: CsvSimple.zip

They are stored as a template and once you open it, you will be prompted to enter the path for your local csv-file.

Where to keep your record?

I think a template is a good way to store your record. Ideally take it as a start for every project where you want to reference it. And if you haven’t started with it, but want to use the record in an existing file, you just open the template, go to “Edit queries”, copy the record and paste it into your existing workbook.

A more advanced way would of course be to use a custom connector, but this requires more technical knowledge and also a PBI pro-license.

And of course this works for pure-M-code as well 🙂 So stay tuned for a blogpost on how to use the function-record as a M-library in Excel via odc-connection.

And if you still struggle with converting your R-scripts to a function, you also have reason to stay tuned, as I will cover that in an upcoming blogpost as well.

Enjoy & stay queryious 🙂

Comments (6) Write a comment

  1. Pingback: #Excel Super Links #140 – shared by David Hager | Excel For You

  2. Isn’t it easier to put all your user defined functions in an M-Extension? Then you can use them the same way as native functions. No record prefix, no web dependency.

    Reply

    • Hi Frank, that sounds like what I’ve been dreaming of for a long time! How can this be done?
      Cheers, Imke

      Reply

      • Hi Imke,

        it’s like a custom data connector without connecting to data. 🙂

        Create the following .pq-file (section document, hopefully it will be readable below), zip it, change the extension to .mez and put it into the one and only folder “C:\users\Imke\documents\microsoft power bi desktop\custom connectors”.

        Done! (There are three user defined functions: Number.Double, Number.Triple and Library. The latter is just for convenience)

        Cheers, Frank

        section MyCustomLibrary;

        Library.Raw = (Text as text) as function => Expression.Evaluate(Text, #shared);

        Library.Params =
        type function (
        Text as( type text meta [
        Documentation.FieldCaption = “Select a user defined function.”,
        Documentation.FieldDescription = “Wählen Sie eine benutzerdefinierte Funktion aus.”,
        Documentation.AllowedValues = Table.Column(FunctionsAvailable(), “Function”) ]))
        as function;

        Library.Documentation =
        [
        Documentation.Name = “Library”,
        Documentation.Description = “Library of user defined functions.”,
        Documentation.Category = “Miscellaneous”,
        Documentation.Examples = {[Description = “Function library”, Code = “Library(“”Number.Double””)”, Result = “function” ]}
        ];

        FunctionsAvailable = () as table =>
        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45W8ivNTUot0nPJL03KSVWK1YGLhBRlFoBEYgE=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Function = _t]),
        ChangedType = Table.TransformColumnTypes(Source,{{“Function”, type text}})
        in
        ChangedType;

        shared Library = Value.ReplaceType(Library.Raw, Library.Params meta Library.Documentation);

        //—————————————————————————————————————–

        Number.Double.Raw = (Number as number) as number =>
        2 * Number;

        Double.Params =
        type function (
        Number as ( type number meta[
        Documentation.FieldCaption = “Number”,
        Documentation.FieldDescription = “The number to be doubled.”,
        Documentation.SampleValues = {2}]))
        as number;

        Double.Documentation =
        [
        Documentation.Name = “Number.Double”,
        Documentation.Description = “Double a given value.”,
        Documentation.Category = “Number”,
        Documentation.Examples = {[Description = “2*2=4”, Code = “Number.Double(2)”, Result = “4” ]}
        ];

        shared Number.Double = Value.ReplaceType(Number.Double.Raw, Double.Params meta Double.Documentation);

        //————————————————————————————————

        Number.Triple.Raw = (Number as number) as number =>
        3 * Number;

        Triple.Params =
        type function (
        Number as ( type number meta[
        Documentation.FieldCaption = “Number”,
        Documentation.FieldDescription = “The number to be tripled.”,
        Documentation.SampleValues = {2}]))
        as number;

        Triple.Documentation =
        [
        Documentation.Name = “Number.Triple”,
        Documentation.Description = “Triple a given value.”,
        Documentation.Category = “Number”,
        Documentation.Examples = {[Description = “3*2=6”, Code = “Number.Triple(2)”, Result = “6” ]}
        ];

        shared Number.Triple = Value.ReplaceType(Number.Triple.Raw, Triple.Params meta Triple.Documentation);

        Reply

  3. Pingback: Create a function library in Power BI using M-Extensions – The BIccountant

Leave a Reply