In this post we’ll look at some simple examples of how to use R in Power BI.
While going through these examples, we’ll have a look at what is already possible in this preview version and what is not possible.
We’ll do this by slowly examples from scratch so you can follow or rebuild the examples yourself.
I’ll keep the technical explanations to a minimum as there are already a ton of books and courses filled with that.
So if you’re seeking deep knowledge of the inner workings of R, this post is going to disappoint you.
However, if you’re looking to keep it simple and just get started building cool or useful visualizations in R to explore your data, just keep reading!
In case you missed the previous posts:
In part 1 of this series, I gave you a couple of reasons to start using R for visualizations in Power BI.
In part 2 of this series, I gave you remarks on the R integration. You might’ve caugtht a glimpse of what is possible if you stepped outside of the lines that Microsoft drew. And I showed you an ugly error you can receive because of decimal symbol setting in your OS.
Before we can start using R in Power BI, there are 2 things we need to do.
- Install an R distribution
- Point Power BI to the R distribution and enable the Preview Feature
- Via the File menu, go to the options window.
- Select “R Scripting
Type the path where you installed the R distribution from the first step.
The path you need to type will vary from the screenshot depending on the distribution, version and installation directory.
- Next, select “Preview Features”.
Check the box at “Use an R script to plot a visual in the canvas
- Apply the change and restart Power BI when you are asked to do so
- Via the File menu, go to the options window.
Now you can start using R scripts to create visualizations.
You need 2 things, a dataset and a R script.
The first thing is something we all have, the 2nd is what we’ll learn here!
“Hello world!” in R
This shows a hard requirement for every one of our R Visual scripts that we are going to create for Power BI.
“Hello visual world!”
So instead of printing “Hello world” to the screen, we’ll use a simple graph to say hello to the world.
First we need some data, Power BI enables us to enter some data in a familiar Excel style.
Just select “Enter Data” and start bashing out some data.
After pressed the load button, you’ll see the dataset appear on the right hand side of the screen.
Now it’s time to add an R Script Visual. Click the blue R in your Visualization pane
You’ll see a blank visualization appear. When you click it, the R script editor will appear.
But you’ll notice that the R script editor is blank as well.
This is where the brilliance of the Power BI team shines yet again.
They could have made it very difficult to get that data from whatever source you’re using into an R script visualization. Instead, they opted to keep the already familiar way of working.
You simply have to select a (blank) visualization and then select the fields that you’d like to use in it.
In our example here, we selected both fields (step 1).
We then automatically see them appear in the Values field list and the Filters field list.
This works exactly as we know from regular visualizations.
Having selected these fields, something else happend.
We now see the following things in the R script editor
1. The fields that we selected are automatically selected here as well
2. Power BI put the fields in a dataset for us (that dataset is called “dataset”)
3. We can now actually type a script if we want to.
The hard part, creating a visualization
Let’s start with a simple visualization. a regular plot.
In order to visualize our data, we have to put the fields from our R data into variables.
Luckily, this is easily done in R by typing:
- A variable name
- A left pointing arrow “<-“
- The field that you want to assign to the variable.
Below code does all that AND it create a basic visualization using the easy-to-use plot function.
If you’re wondering what the hashtags do, I’ll explain.
They allow you to type comments that make it easier for others to read your code.
And in some cases, they even help yourself when you have to re-read your own code after a while.
So these 3 short lines of code created a simple plot that looks like this in my case:
Not so fancy if you ask me…
Let’s give it a makeover!
#DataViz is in the details
We used the plot function without any options. That’s why we got that simple, quite ugly, graph.
But what are all the options that we can use?
That’s where the documentation comes into play. Quickly bookmark www.rdocumentation.org so you’ll never forget it!
On that website we’ll find the documentation to the plot function
Playing around with all the options can give you a lot of power over your visualization.
In this case, sadly enough, we’re very limited by our dataset to create something useful.
You’ll have to forgive me for dumping below plot in here, it’s mainly to showcase all the options that were used.
In this post we learned several things
- Enabling the R script visualizations in Power BI
- R scripts always need a visualization as output to be used in Power BI
- We created our very 1st R script visualization
- It’s very easy to get started with R script visuals, creating beautiful visuals will require a lot of work however.
In the end we didn’t create a very beautiful visualization.
There are 2 reasons
- We didn’t have proper data
- We were only using the standard plot function
Like in a regular BI proces, keeping the form of your data in mind is very important.
You might need to create custom columns / features or even different aggregations to get the visualization that you need.
In my next post I’ll be exploring the new version 2.0 version of the ggplot2 package.
ggplot2 is a well known visualization package in R and has a lot more functionality than the standard plot function.
If you can hardly wait for the post, go check out this ggplot2 cheat sheet.
In the posts after that, we’ll explore some other R functionality that can be used like Machine Learning.
But most important, we’ll go from playing around to making useful reports!