Reference: Power BI Introduction: Working with R Scripts in Power BI Desktop — Part 3, by Robert Sheldon.
In previous post I was focusing on using R script to develop visuals in PowerBI directly. In that case, the R scripts that are integrated with the visuals don’t generate datasets for PowerBI, and the R scripts are isolated for each visuals.
The PowerBI and R scripts can also be integrated to get and transform dataset. The caveat is the R scripts can only generate and transform one dataset at a time through Power Query Editor.
Get Data:
- File / Home Ribbon > Get Data > More … > Other > R Script > Connect
- The “cars” dataset appears in the “Model” view.
- Click “Edit Queries” on the Home Ribbon to get into the “Power Query Editor”
- Select “Car” from the “Queries” list
- In the “Query Settings” the “Applied Steps” will show up in the sequece
- To edit the original R script that gets the dataset, click the * besides the “Source” step (first step) and the “R script” box will appear. If the “Query Settings” is now showing, click “View” ribbon and “Query Settings”.
Transform Data:
- To create another dataset that contains the mean distance by speed group.
- Repeat the same GET DATA steps above and name the dataset “cars_mean”
- Get into “Power Query Editor”, select “cars_mean” table.
- Click “Run R Script” on the “Transform” ribbon and enter the R codes that are showing below.
- Unless in the sources step, you have to reference the dataset as “dataset” rather than the name of the table, eg “cars_mean”.
More Complicated R script for Source step:
- In the Source step, you can refer the dataset with its name. In this example, it is cars.
- The R script needs to return a table. If the script only produce a value, you need data.frame() function to convert it to a table.
- Make sure the required R package have been installed in the R library.
- Use library() function to attach all the required libraries.
Advanced Editor:
