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:
will export content of my query “Actuals” to csv-file on my desktop.
- 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.
- followed in square bracket is the name of the function (record field name)
- in ordinary brackets you have the function arguments just like in standard M (record value)
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:
- Paste function code
- Add a name, followed by an equation-sign to it
- 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:
- Copy function code and paste after the opening square bracket
- Add a comma to separate from existing code
- 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:
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”:
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 🙂