Especially if you are new to R, there are some things one needs to know to successfully run R-scripts in the query editor of Power BI. I will share them here along with some tricks that made my R-life in Power BI easier:

How to get started – useful links:

• https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-in-query-editor/
• https://querypower.com/2017/03/11/r-execute-the-swiss-army-knife-of-power-query/

Input:

You can feed multiple tables into the R-script

If you click the icon “R script”, the table from the previous step will automatically be passed as the “dataset” to the R-script. So if you don’t fill in any R-code, this will happen:

But if you need the content from other tables as well, you just add them into the square brackets like this:

“Documentation” looks like this:

You can use parameters in the R-script

Apart from tables, you can also use text strings as parameters in the script. They need to be inserted into the code with preceeding “& and trailing &”:

RExportCsv= R.Execute(“write.csv(dataset,” “&CsvExportPath&” “)”,[dataset=Actuals])

Beware that they must be text. So if you want to pass a number, wrap it into Text.From(…).

You cannot use anything else apart from tables and parameters in the R-script

Well, at least I haven’t managed it

R-life gets easy-peasy if you use M-functions for your R-script

For an R-script like export to csv for example, you have to reverse all slashes in your path-definition. This is a bit tedious. So just create an M-function that reverses them automatically (row 4) before passing to the R-script like this:

Another advantage: You can create a nice user-dialog by adjusting the function metadata like this for example:

So the user doesn’t have to see that R-code is used here.

Metadata-adjustment starting in row 18. This post describes the details for adding the function-metadata: http://www.thebiccountant.com/2017/05/11/how-to-edit-m-function-metadata/

Output:

Output of the R-script has to be a data-frame

This one has fooled me a bit: My script worked fine in RStudio, but didn’t return any results in Power BI. As it turned out, the result of the script was a list (or vector) and not a dataframe. So I had to convert it to a dataframe (see row 12 & 13 in R Trend m) and then it worked alright.

You need to assign desired outputs

This might also not be too obvious for beginners: You can successfully run R-code, but not get any result returned from your R-script: Check the ExportCsv-script above: It would export the table to the desired path without the code in row 6 (“output <- dataset”), but we wouldn’t be able to see the data or further work on it in the query editor. So if you use the R-script for transformations and then further work on the results or load them to the data model, you need to create an output by assigning via the “<-“.

You can have multiple output entities

If you run the trend-function from above, you will get a table like this back:

outTrendline contains the trendline for the actual figures and outForecast the trend-predictions for the future periods (…the assignments from row 8-11 are not returned here, because they are not in dataframe-format).

I hope this will help getting you started quickly with R in M !

Do you agree that it’s a real pity that we don’t have the R-integration in Power Query in Excel (yet?)?. Then please vote for this feature here.

Also check out this post explaining how to create and use a function library for your R-functions and applications.

Enjoy & stay queryious 🙂