Intro to R with Power BI

In Business Intelligence & Analytics

Intro to R with Power BI - read the full article about business intelligence, Business Intelligence & Analytics and Data analytics and consulting from Guy in a Cube on Qualified.One
Guy in a Cube
Youtube Blogger

- Yooo! Whats up? This is Patrick from Guy in a Cube.

And in this video, Im gonna do a quick introduction to R in Power BI.

Stay tuned.

(upbeat music) If youre finding this for the very first time, be sure to hit that subscribe button to stay up to date from all the videos from both Adam and this guy.

All right, I did a video on introducing you guys to Python in Power Bi so I decided to do one introducing you to R in Power BI.

So you guys know what I like to do.

Instead of all this talking, lets do what? Lets head over to my laptop.

The first thing you got to do, just like Python, you go to File, Options and Settings, and Options.

And Ill be honest, this was much, much simpler than doing the Python script.

Then you have your two choices, R scripting, Python scripting.

Choose R and you can see Ive already installed it.

Theres lots of different flavors.

If youre not sure which one or what to go and do, just click on how to install R.

Itll take you to this link right here, and then itll walk you through what you need to do.

If you just click on this download page or this download page or whichever version of R you want to install, itll open up this link and then you can go and choose which one you want based on the machine.

Obviously, youre in Power BI desktop, its gonna be Windows.

So once you do that, then youll have options here to choose which version of R you want to run.

So you can see Im using the R server, but it doesnt matter.

And then choose the IDE.

Im just using the default one for my operating system.

So were gonna click OK.

Once you do that, just like with Python, you can import and prep and massage and get intimate with your data with R and you can visualize your data with R.

Im gonna show you both.

So first, were gonna get some data.

So we choose Get Data, More, and then the little dialogue window will pop up.

And if you type R scripting, youll see there goes R script and you click on R script and you click Connect.

And then right here, its really simple.

You type data and theres a list.

I just did a search out on the interwebs.

And I found a page that had so many R datasets, and you go find one.

I like this mtcars right here, Motor Trend Car Road Tests.

So I just picked that one.

So you go here, you type data, mtcars, and you can do other things here also, but this is the simplest and the purest form of it.

Click OK. Its gonna do its thing behind the scenes.

Open up the navigator window, and then you can see my mtcars dataset.

So well do just like that and then you can see all my data.

Im gonna go ahead and click Transform just so you can see that if I want to, I can go and do some transformations of the data.

And you can see right here, am means transmission type.

I believe zero is automatic and one is manual, but that matter, right? Ive connected to this data with R.

If you right click on the query itself and go to the Advanced Editor, you can see that the M is pretty simple.

If I go to Add Column with that am column selected, and I do Column From Selection, I can go, if am is one, this will be manual and if am is zero, its automatic.

Its column from example for me, Transmission Type.

And then I click OK.

I can import my data, I can do the transformations in R or I can do it in Power Query.

Completely up to you.

Im gonna go ahead and click Close and Apply.

Ive just imported my data with R then I did some transformations with M on Power Query.

And now Im gonna visualize my data with R.

Its pretty cool. Its pretty simple.

So now you click on the white space on your report.

Youll see over in the Visualization pane, you have the Python visual and the R visual.

Go ahead and click the R visual, get the same prompt.

Lets go ahead and enable it.

And now, we need to drag the fields that we want to use in our R script into the value section for that element.

So Im gonna go ahead and expand this out and Im gonna drag over miles per gallon and the number of cylinders.

What you need to make sure is that the columns that youre gonna use, you set the default summarization to none.

So the very first one, it should be...

Now its set to Sum so were gonna say "do not summarize".

And then for cylinder, were gonna set it to "do not summarize".

And then we can start writing our script.

So what I want to do...

I dont want to create a visual thats already there.

So what Im gonna do is create a box plot.

And so this code is automatically generated for us.

It automatically creates this data frame to remove duplicates when its executed.

So Im actually gonna use this.

And so what were gonna do is type boxplot.

And then Im gonna say data set, dollar sign, and Im gonna use miles per gallon, look at the IntelliSense and then Im gonna say data set, dollar sign.

When I run this, we should get our box and whisker plot.

Just like that.

Just a really small sample of R scripting in Power BI.

Whats nice about it though, like with the Python visual, everything interacts.

Remember that Transmission Type column that I created? If I drop that onto the design surface and use my slicer and click it, youll see how things change just a little bit based on that.

The interaction between the two vision is fully built in.

Now, you can take this to the next level.

And I already wrote a script.

If you want to add titles to your access, into your report, and things like that, I go to the formatter, I could turn off the title, and then Ill just do like a cooking show, paste this in and go ahead and run this script.

And then, boom, youll see how nice and fancy it is.

Its got colors and different things like that just based on my R script.

Its pretty easy, didnt do a whole bunch of work, but you know, there it is.

You can add so many properties to this box plot.

Now, before I go, there are some limitations.

You need to make sure you go and read the full documentation here.

Go to the Known Limitations sections and be aware of that.

Learn about the requirements and limitations of the R packages that are available in the service.

The last thing, just like Python, you need to have the personal gateway if you want to refresh these data sets and once you publish them out to Power BI.

All right, what do you guys think? Do you have any questions, comments? Are you using R? I love to know.

Lets continue the conversation, where? In the comments below.

If its your first time visiting the Guy in a Cube channel, hit that subscribe button.

If you like my video, big thumbs up.

As always, from Adam and myself, thanks for watching.

See you in the next video. R!

Guy in a Cube: Intro to R with Power BI - Business Intelligence & Analytics