Power BI and R – Part 3 (Getting started)

This entry is part 3 of 3 in the series Power BI and R

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.


Getting started

Before we can start using R in Power BI, there are 2 things we need to do.

  1. Install an R distribution
    1. Standard: Straight from CRAN (Comprehensive R Archive Network)
    2. Enhanced: Straight from Revolution Analytics
  2. Point Power BI to the R distribution and enable the Preview Feature
    1. Via the File menu, go to the options window.
      EnablingR01
    2. 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.
      EnablingR01b
    3. Next, select “Preview Features”.
      Check the box at “Use an R script to plot a visual in the canvasEnablingR02
    4. Apply the change and restart Power BI when you are asked to do so
      EnablingR03

What now?

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

Let’s get to work with a simple and very common example.
A “Hello world!” script!
ScriptHelloWorld

Instead of displaying a cool graph, this shows us the following error message.
7. No Image Made

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.

HelloVisual01
HelloVisual02

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
EnablingR04

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

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

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

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:

  1. A variable name
  2. A left pointing arrow “<-“
  3. 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.

EnablingR08If 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:
EnablingR09

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.

EnablingR10


Conclusion

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

  1. We didn’t have proper data
  2. 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!

 

I’m looking forward to your thoughts and comments.
Leave them down below in the comment section, on LinkedIn or on Twitter!
And don’t forget to follow me 😉

Series Navigation<< Power BI and R – Part 2 (Remarks and errors)

7 thoughts on “Power BI and R – Part 3 (Getting started)

  1. Thanks Jan. This was very insightful and you managed to make this very do-able due to the simplistic and concise way you have explained it. Definitely living up to your moto – enabling analytics for everyone.

  2. Very helpful!! Looking forward to your next tutorial.
    Is it possible to display a table with custom column colors?
    Because the pb table isn’t very customizable..

Leave a Reply

%d bloggers like this: