Thanks to the December 2015 update released for Power BI, we can now use R to visualize our data in Power BI! Make no mistake, this is huge news and in this blog post I want to walk you through how to use the new R Script Visualization in Power BI and get you started with using R to create your first visualizations.
Read These Top 5 Power BI Tips
Not only can we create and download custom visuals from PowerBI.com to extend the capabilities of Power BI, we can use R to create a ridiculous amount of powerful visualizations. If you can get the data into Power BI, you can use R to perform interesting statistical analysis and create some pretty cool, interactive visuals.
Getting Started
If you’re new to R, like myself, R is a programming language for statistical data analysis. The R programming language is widely used by statisticians and data scientists and has been around since the early ’90s. R is used to perform advanced types of analysis and create graphic visuals. So what does this mean for Power BI? We can do some pretty awesome things regarding data analysis and visualization!
Disclaimer: I don’t pretend to be a statistician, data scientist, or R expert. I’m just a regular dude who loves data and likes helping my customers gain deeper insights into their data. With this in mind, I’m just sharing with you, the reader, what little bit I know in order to help others learn. Enjoy!
Before we can jump into R, there’s a few things we need to do first to get Power BI set up for this. 
First, go download the latest version of Power BI Desktop.
Now, go download Revolution R Open. Follow the instructions and install RRO.
After you’ve done that, start up Power BI Desktop. We need to enable the R script preview feature. Go to File > Option and settings > Options.
Go to the Preview Feature tab and click the checkbox to Use an R script to plot a visual in the canvas.
Then go to the R Scripting tab. Provide the location where RRO was installed. I just used the default location, as you can see below.
You should then receive a message asking you to restart Power BI Desktop. Close Power BI Desktop and open the tool again.
Once you’ve restarted Power BI Desktop, the R Script Visualization visual should then appear in your Visualization toolbox.
Now you’re ready to start using R to be all data scientist-y!
Important note: The R Script Visualization feature in Power BI is currently a preview feature. This means that the way it works could change a little bit or a whole lot at some point in the future. This also means that the R Script Visual is not currently able to be refreshed in the Power BI online service. Personally, I would expect this to change soon but can neither confirm nor deny any of this.
Creating R Script Visuals with Power BI
Now we just need to get some data into Power BI! To me, this is simply amazing. Power BI can connect to almost any data source, massage and transform the data, and thus preparing your data for data analysis with R. Mind blowing!
The data I’m using for these example is some enrollment Census data I’ve downloaded from census.gov. You can find the same data here, if you’re so inclined.
After you’ve imported your data sources and created the model, add the R Script Visualization to your report. Then drag the fields from the model into the Values area of the R Script Visualization.
As you add fields to the R Script Visualization, you’ll notice some code automatically appear in the R script editor window.
This code creates a dataframe object called “dataset” and then removes the duplicate rows from the dataframe object. This code is created by default and to my knowledge cannot be modified. This could change in the future. A dataframe is similar to a table and contains columns that contain data. With the dataframe, “dataset”, created, we can then begin to create some very simple R visualizations.
To create a very simple scatter plot, use the plot command. The first argument is the values to display on the x axis and the second argument is the values to display on the y axis. In the example below, my year is displayed on the x axis and the enrollment numbers are displayed along the y axis:
plot(dataset$Year, dataset$Enrolled)
~
After you’ve entered in the R code, click the Run button to execute the R script and display the visual.
We can add a few enhancements to the scatter plot, like custom labels for each axes and a chart title using the following code:
plot(dataset$Year, dataset$Enrolled, col="Green", xlab="Year", ylab="Enrollments", main="Enrollments Over Time")
The col argument allows us to change the color of the data point, xlab and ylab allow us to customize the axes labels, and main allows us to specify the chart title.
So that’s pretty simple, But we can also do some more advance stuff, like add a best fit line, boxplots, coplots, histograms, and more.
Here’s adding a blue best fit line to the scatter chart:
plot(dataset$Year, dataset$Enrolled, col="Green", xlab="Year", ylab="Enrollments", main="Enrollments Over Time") bfl
In my enrollment dataset I have enrollment numbers for different age groups and types of students (under grad, 2 year, graduate). Wouldn’t it be cool if we could see one scatter plot for each age group and then filter by type? Well, I’m in luck! I can use coplot to create a scatter plot for each AgeGroup category:
attach(dataset) ### use attach(dataset) to attach "dataset" so I don't have to type dataset$ everytime I want to reference a field ### coplot(Enrolled~Year|AgeGroup, col=3, main="Enrollment by Age Group",type="o", rows=1) ### "type = o" changes the plot to be circles over a line, "rows = 1" forces all charts on 1 row ###
So that’s pretty cool. But we can take this one step forward and create a matrix layout for our line graphs using coplot. I want to see one plot for each AgeGroup and each Type:
attach(dataset) ### use attach(dataset) to attach "dataset" so I don't have to type dataset$ everytime I want to reference a field ### coplot(Enrolled~Year | AgeGroup * Type, col=3, main="Enrollment by Age Group",type="l") ### "type = l" changes the plot to be circles over a line ###
How awesome is that? Now here’s the real kicker: I can slice and filter these visual through the usual methods! I can use another chart or a slicer to filter! Don’t believe me?
If that’s not flipping awesome then I don’t know what is. Each time I click the slicer or donut chart, the R script is rerun with the filter criteria thus generating a new visualization of the data. Just keep in mind depending on the R script you’ve written, the amount of data, and other factors, this could be slow.
In all honesty, this cross filtering action doesn’t appear to be working on PowerBI.com yet, but will most likely be soon. I can’t speak for the Power BI engineering team, but I’d imagine they’re working on it.
Check out this boxplot chart:
attach(dataset)
boxplot(Enrolled~Type, type="l", horizontal=TRUE, col=(c("Blue","Gold","Red")), notch=TRUE, xlab="Enrolled", ylab="Student Type")
And a histogram chart:
attach(dataset) hist(Enrolled, col="purple")
So that’s pretty much the basics. I haven’t even touched installing custom packages for other visualization types but that’s definitely a blog post for another day.
I hope you found this help! I’m very excited about this new functionality and can’t wait to see some of the awesome visuals people will put together!
Resources
Download my Power BI Desktop file that includes the examples and data sources I used in this blog post and more.
Helpful Links
Very helpful site with some good info on creating basic visuals with R: http://www.statmethods.net/
Creating R Visuals with Power BI: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-visuals/
Jan Mulkens has a helpful blog post that may help you if you run into an issue with decimal settings in your OS: http://blog.janmulkens.be/power-bi-and-r-part-2-remarks-and-errors/
Feedback?
I’d love to hear what you think! Leave a comment or question down below to share your experiences with your fellow data nerds.
great for getting started.Which dataset exactly did you use – page ref points to an index page of many datasets
I used Table A-7, which is the one near the bottom I believe. I didn’t use the whole dataset either, but rather just one section for simplicity’s sake.
Is Power BI also available for mac??
The Power BI service is supported on Apple devices. You can log into your Power BI service, create, share, collaborate, and more. There are also iPhone, iPhone, and Android apps that allow you to view and share your Power BI reports. The only thing that isn’t supported on Apple devices at this time is the Power BI Desktop, which is the desktop authoring tool. I hope this helps.
Using this post as a starter I connected to my facebook posts and used the following code to create a nice wordcloud of my top 100 words used on FB, just like the “app” that was doing it a few weeks back:
#install.packages(“tm”)
library(tm)
#install.packages(“wordcloud”)
library(wordcloud)
#install.packages(“RColorBrewer”)
library(RColorBrewer)
attach(dataset)
message <- Corpus(VectorSource(message))
message <- tm_map(message,removePunctuation)
message <- tm_map(message , tolower)
message <- tm_map(message , function(x) removeWords(x, stopwords("english")))
message <- tm_map(message,removeWords,c("just","new","sqlhelp"))
message <- tm_map(message , PlainTextDocument)
wordcloud(message, scale=c(5,0.5), rot.per=0.35,colors = brewer.pal(8,"Dark2"),max.words=100)
Cool! 🙂
Hi Ryan,
I loved it . My question is ” Is Power BI a Free software like R? also you did not show us the data sources.
Thanks
Sam
Power BI Desktop is free to download (https://powerbi.microsoft.com/en-us/desktop) and there is a free option for the Power BI service. But to get the most out of Power BI for your organization I recommend the Power BI Pro License (https://powerbi.microsoft.com/en-us/pricing ).
I’d recommend checking out this blog post to get a better understanding of how Power BI works: http://sqldusty.com/2015/07/24/power-bi-desktop-designer-my-first-run-through/
Great article. One clarification on this sentence: ” A dataframe is similar to a table or matrix and contains columns that contain data”
A matrix in R is a two dimensional object that contains a single data type, usually numbers. There are many built-in functions for matrices like Inverse and Transpose that do not apply to a dataframe .
A dataframe is a collection of vectors that can be different datatypes like a table in SQL Server.
Thanks for your article.
Great! Thanks for the input and clarification. 🙂
hi there
I got windows 10 on Surface 3 and installed PowerBI desktop 64 bit but it doesn’t seem to remember the location of my R. I followed your tutorial above, close PBI and when I re open it the path to R isn’t there anymore. can you advice please? Many thanks Nujcharee
This issue is solved with the Power BI Desktop January update. See here: http://blogs.msdn.com/b/powerbi/archive/2016/01/28/power-bi-updates-this-week-new-report-authoring-capabilities.aspx
Thanks, Moshe! 🙂
Thanks Dusty! Look forward to going over this with our meetup group tonight.
Thanks for writing the article Ryan!
Quick question. I created a boxplot visualization using the ggplot2 library. When I try to run the code nothing happens… no error message.. nothing.
I tested the code in R Studio and it work. I created a dataset by adding the necessary fields to the R custom visual tool and I pasted the code in the script editor and changed the data frame that my code was referencing to “dataset”. Any idea of what I am doing wrong?
Can you email me the pbix so I can take a look?
Here is my code:
install.packages(“ggplot2”)
library(ggplot2)
p <- ggplot(dataset, aes(dataset$field1, dataset$field2))
p <- p + geom_boxplot()
p
My first comment did not post. A modified version of the code above works in R studio but not in Power BI. The only modification I made in Power BI was I changed my dataset. When I try to run the code nothing happens. No error message… nothing. Any idea what is going wrong. Is it Power BI or is it something I am missing in my code?
Please disregard the previous messages. I was tired and I did not see the button to enable the custom R visuals. Lol. Everything thing is working fine now. This feature is powerful!
LOL it’s always the last thing you check! 😉 This feature is awesome!
I am not sure whether mny earlier comment went. Does publish work with R visuals ?
At this time publishing the R visuals does not work, but the Power BI team has stated that this feature is on the roadmap. 🙂
Dustin – I just found your site, and I’m really enjoying the content. I’m a long-time R & T-SQL user but very green with Power BI, and I’m excited about the new integration with MS products. This is a great post, and, in the future (if you’re continuing down the R rabbit hole) it’ll save you some time and heartache to invest in learning ggplot2 for R graphics. It’s the standard for R users now, and it’s capable of making gorgeous visuals with extremely parsimonious code. Thanks for the post!
Thanks for the post, it was just what I was looking for.
No problem! Glad it was helpful!
Very good Dustin. I am learning R at my own snail’s pace so thought I’d give R in Power BI a go. I have created three charts in my first session and I am happy with that. However,
plot() gave me a boxplot … I’ve coloured each box individually, put a notch in … looks suave but it’s not a scatterplot!! Code:
plot(dataset$ServerName, dataset$Download, xlab=”Server Name”, ylab=”Download Speed”, main=”Download Speeds by Server Name”, notch=TRUE, col=c(“Red”,”Orange”,”Yellow”,”Green”,”Blue”,”Purple”,”Violet”,”White”))
I tried again and this time plot() gave me a scatter type plot but with horizontal bars not dots! Code, using attach this time:
attach(dataset)
plot(ServerName, Upload, main=”Upload Speeds by Server Name”, xlab=”Server Name”, ylab=”Upload Speeds”)
I tried again and got a scatterplot! Ta daa! The values on the X axis need tweaking as they run into each other Code:
plot(Date, Download, type=1, col=”purple”, main=”Download by Date”, xlab=”Date”, ylab=”Download Speed”)
I tried hist() and got a one box output … not what I was expecting at all!
I am using my own data set which is the reports of my wifi speeds from various servers and so on: not in the least bit complex:
Date, Connection Type, Download, Upload, server Name …
Anyway, this page of yours is very useful but those things have thrown me!
Glad you found it useful! Good luck!
Hi Dustin.. can i save variable created through R script in power BI table
Yep. Take a look at this: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-r-scripts/