PowerBI and R Integration – Data Manipulation using R script

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.

3 examples: cars (simple R script in Source step to GET DATA); cars_mean (R script in Source step and Run R script step); cars_co (more complex R script in Source step)

• 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”.

Source step in Power Query Editor for table cars.

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

Run R script step in the Power Query Editor for table cars_mean

cars_mean table in the Data View.

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.

R script in the Source step for table cars_co in Power Query Editor

cars_co in Data View

Advanced Editor: