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:
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 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.
Enjoy & stay queryious 🙂