Category Archives: Business Intelligence

R and Power BI Working Together

Microsoft recently introduced R integration capabilities in its Power BI offering stack (still in beta preview at the time of writing), and it is quite impressive. In order to get started with R and Power BI you need to download and install a distribution of R – recommended Microsoft R Open (MRAN) with Intel MKL library, and Power BI Desktop

R integration with Power BI essentially breaks down into two parts:
I) Getting R data into Power BI and/or Power BI Desktop
II) Visualizing and analyzing data using R scripts in Power BI Desktop (no Power BI portal support yet)

There are a few great articles on R / Power BI integration:
– Running R Scripts in Power BI Desktop (Beta)
– Create Power BI visuals using R (Preview)
– Visualizing and operationalizing R data in Power BI

However, there are some steps that are missing in the documentation article Create Power BI visuals using R (Preview). The next 3 short paragraphs will address all the missing steps to get that guide fully working:

1. Getting R data in Power BI Desktop or Power BI – in our example will take one of the standard R sample datasets, which are also referenced in Microsoft guides – mtcars.
For the full list of available R sample sets check out: The R Datasets Package
Open Power BI Desktop, select Get Data from the ribbon bar and provide the following one-liner script, when specifying R Script as a source of data:



2. Adding additional visualization packages to R – in order to add an external package to R, such as corrplot, we will use install.packages() command.
A couple of useful links:
– Available CRAN Packages By Date of Publication
– An Introduction to corrplot package
Open R GUI environment and run the following script:

M <- cor(mtcars)
corrplot(M, method = "number")


3. Visualizing data using R script in Power BI Desktop – insert R visualization side-by-side with other standard Power BI charts for the same data.
Once you connect to external R sample data outlined in step 1, insert R chart and select all fields from mtcars dataset so they will appear in dataset in R script editor.
After you selected all fields and Power BI Desktop generated dataframe command: ‘dataset <- data.frame(hp, mpg, qsec, vs, wt, am, carb, cyl, disp, drat, gear)’, execute this script:

M <- cor(dataset)
corrplot(M, method = "number")