Consume R functions from Power BI

This post is about calling R functions from Power BI. The use case for this is utilising the functions created by Data Scientists i.e. scoring alogrithms, predictions on churn, risk assessment models and more.

R Plumber provide a package that allows you to serve up R functions with a REST based web API.

I know from previous experience that Power BI can query REST API's so I decided to give it try.

R Plumber

First thing is to prefix R functions with attributes as per the R Plumber docs.

To keep this simple I used the sample code in the R Plumber docs.

#* Echo back the input
#* @param msg The message to echo
#* @get /echo
function(msg=""){
  list(msg = paste0("The message is: '", msg, "'"))
}

#* Plot a histogram
#* @png
#* @get /plot
function(){
  rand <- rnorm(100)
  hist(rand)
}

#* Return the sum of two numbers
#* @param a The first number to add
#* @param b The second number to add
#* @post /sum
function(a, b){
  as.numeric(a) + as.numeric(b)
}

To serve up these functions you can use this code to launch the server on port 8000 on localhost.

library(plumber)
r <- plumb("plumber.R")  # Where 'plumber.R' is the location of the file shown above
r$run(port=8000)

I was primarily interested in calling the /sum function. This requires a POST request though so we need to get our hands dirty with M.

I remember creating a POST request several years ago when Power Query was called Data Explorer. Would the same technique work I wondered.

The first thing I wanted to do was test the end point. If on Linux/Mac you can use Curl to do this.

I ended up using Postman on Windows.

Notice the request is POST and we need to specify parameters as per the function signature. I see the results being returned, great, plumbing done.

##Power BI

To call this function from Power BI you need to write M code as the standard Web source only makes a GET request.

This data comes into Power Query initially as JSON and ended up in a LIST. A few extra steps are needed to turn this into a TABLE and change the data type from text whole number.

The key M code is listed below. Note the addition of the Content parmeter. When you use the Content parameter the web request changes from GET to POST. The text parameters also need to be encoded in Binary, hence Text.ToBinary()

Web.Contents("http://localhost:8000/sum", [Content=Text.ToBinary("a=5&b=10")])

The complete M code is listed below

let
    Source = Json.Document(Web.Contents("http://localhost:8000/sum", [Content=Text.ToBinary("a=5&b=10")])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Score"}})
in
    #"Renamed Columns"

Of course in the real world you will need to authenticate to a deployed web service. If I had more time I would love to see how far this goes i.e published to the Power BI service and with a data frame rather than single value, it should work in theory but I've not tested this.

Summary

You have multiple options to deploy R code depending on the infrastructure and tools you have. You now have an open source method of deploying R functions.