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:


let func =
(FunctionRecord as record) =>
let
Source = Record.ToTable(FunctionRecord),
Meta = Table.AddColumn(Source, "Meta", each Value.Metadata(Value.Type([Value]))),
ColNames1 = Record.FieldNames(Record.Combine(Meta[Meta])),
#"Expanded Meta" = Table.ExpandRecordColumn(Meta, "Meta", ColNames1),
Expand1 = Table.ExpandListColumn(#"Expanded Meta", "Documentation.Examples"),
ColNames2 = Record.FieldNames(Record.Combine(List.Select(Expand1[Documentation.Examples], each _<>null))),
#"Expanded Documentation.Examples1" = Table.ExpandRecordColumn(Expand1, "Documentation.Examples", ColNames2),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Documentation.Examples1",{"Value"})
in
#"Removed Columns"
, documentation = [
Documentation.Name = " fnRecordFunctionsToTable
", Documentation.Description = " Transforms the function-record to a searcheable table showing metadata
", Documentation.Category = " Record
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com
", Documentation.Examples = {[Description = " 1) Reference function-record as parameter
" , Code = " Check this blogpost explaining how it works: http://wp.me/p6lgsG-Gx
", Result = "
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Record.FunctionToTable.pq

hosted with ❤ by GitHub

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:


let func =
(MasterPath as text, BlobPath as text) =>
let
/* debug parameters
MasterPath = "https://github.com/ImkeF/RM/tree/master",
BlobPath = "https://github.com/ImkeF/RM/blob/master",
*/
FileEndings = {".rm", ".m"},
Source = Web.Page(Web.Contents(MasterPath)),
Data0 = Source{0}[Data],
#"Filtered Rows" = Table.SelectRows(Data0, each List.AnyTrue(List.Transform(FileEndings, (list)=> Text.EndsWith([#"Failed to load latest commit information."], list)))),
CreateURL = Table.AddColumn(#"Filtered Rows", "URLFunctionCode", each BlobPath&"/"&[#"Failed to load latest commit information."]),
GetCode = Table.AddColumn(CreateURL, "GetCode", each Text.Combine(Web.Page(Web.Contents([URLFunctionCode]))[Data]{0}[Column2], "#(lf)")),
#"Removed Other Columns" = Table.SelectColumns(GetCode,{"GetCode", "Failed to load latest commit information."}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Failed to load latest commit information.", "Name"}}),
CleanFunctionName = Table.TransformColumns(#"Renamed Columns", {{"Name", each Text.BeforeDelimiter(_, ".", {0, RelativePosition.FromEnd}), type text}})
in
CleanFunctionName
, documentation = [
Documentation.Name = " fnGetCodeFromGithub
", Documentation.Description = " Fetches function code from GitHub
", Documentation.Category = " AccessingData
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com
", Documentation.Examples = {[Description = " 1) Path to repo main page 2) Path to the single files
" , Code = " Check this blogpost explaining how it works: http://wp.me/p6lgsG-Gx .
", Result = "
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

GetCodeFromGitHub.pq

hosted with ❤ by GitHub

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:


let func =
(TableWithCode as table, CodeColumnName as text) =>
let
Source= TableWithCode,
EvaluateFunction = Table.AddColumn(Source, "Value", each Expression.Evaluate(Record.Field(_, CodeColumnName), #shared)),
Cleanup = Table.RemoveColumns(EvaluateFunction,{CodeColumnName}),
ToRecord = Record.FromTable(Cleanup)
in
ToRecord
, documentation = [
Documentation.Name = " fnRecord.ToFunctionRecord
", Documentation.Description = " Converts a table with function code to a record where the function is active
", Documentation.Category = " Table
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com
", Documentation.Examples = {[Description = "
" , Code = " Check this blogpost explaining how it works: http://wp.me/p6lgsG-Gx
", Result = "
"]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

Table.ToFunctionRecord.pq

hosted with ❤ by GitHub

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.

Edit 2018-May-17: Using #shared in Expression.Evaluate will cause problems when refreshing in the service unfortunately. Find more about it and a workaround here: http://www.thebiccountant.com/2018/05/17/automatically-create-function-record-for-expression-evaluate-in-power-bi-and-power-query/ 

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”:


let
// Fetches your function library from a csv-file: 3 columns "Column1" is autogenerated and will be removed, "GetCode" hold code and "Name" the name of the function
// Adjust "Path" to your own machine:
Path = "C:\Users\imkef\Desktop\ImkesFunctions.csv",
// The Encoding parameters might need to be adjusted to local settings
Source = Csv.Document(File.Contents(Path),[Delimiter=",", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.Csv]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{"Column1"}),
EvaluateFunction = Table.AddColumn(#"Removed Columns", "Value", each Expression.Evaluate([GetCode], #shared)),
Cleanup = Table.RemoveColumns(EvaluateFunction,{"GetCode"}),
ToRecord = Record.FromTable(Cleanup)
in
ToRecord

view raw

GetCodeFromCsv.pq

hosted with ❤ by GitHub

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 (11) Write a comment

  1. 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

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

  3. Hi Imke, I’m a big R user and have developed some functions that I would like to use on Power BI. Therefore this post is very useful, thank you very much. However, there’s a task that I don’t really know how to tackle: I have a R function that runs a model on a data frame and predicts a value for each row. I would like to apply this function in a Power BI dataset and dynamically update a new column so I can plot some new graphs. My biggest concern here is being able to filter the data to be modeled via Power BI’s slice. For instance, the slice would allow me to run the model only on male population from my dataset which will give different predictions then running the model on the whole dataset upfront to the filtering. How would you suggest me to accomplish it? Use M extensions and hit refresh button every time I change my slice is not really an option to me, the updates need to happen on the fly. What I’m doing right know is creating a new Custom Visual with plotly (see http://radacad.com/interactive-charts-using-r-and-power-bi-create-custom-visual-part-2). What I don’t like with custom visual approach is that there’s no total interactivity between the visual plot and the other “default” plots from Power BI. Hope you can understand my question. Thanks in advance, Gabriel.

    Reply

    • Hi Gabriel,
      the only other alternative I see is to create a large table in the query editor, where you fetch the results from your R-scripts for every possible slicer (-combination) (and have them in an additional column). So your users can filter on them in the model. The engine in the data model is so powerful, that you can feed in reeeally long (thin) tables without performance problems 🙂
      Just to say that I’m not familiar with custom visuals, so cannot confirm that there is no workaround to the limitation you’ve described.
      Cheers, Imke

      Reply

  4. Pingback: Automatically create function record for Expression.Evaluate in Power BI and Power Query – The BIccountant

  5. Pingback: Using a record as a custom function library – Beyond Basic Power BI

Leave a Reply