Introduction to R Scripts in Power BI Desktop

20 December, 2017 (10:20) | Power BI, Power BI Desktop, R | By: Mark V

Since I started learning R, I wanted to play a bit with the use of R scripts in Power BI Desktop. Note that my R skills are very basic, so please don’t use this post as an example of top-notch R.

At this time, there are three places in Power BI Desktop where you can use your own (or blatantly stolen from the Internet) R scripts. R can be an excellent option for adding prediction to your data models. For each, I will demonstrate how to do it as well as show the overall conceptual data flow. For Report View, I will show two examples since there are two conceptual data flows that are supported when using an R script in Report View.

1. Query Editor – Source

2. Query Editor – Transformation

3. Report View – Using Power BI Desktop Data Model

4. Report View – Bypassing Power BI Desktop Data Model

There will be some overlap between my content here and the official Microsoft documentation via the sites below. My hope is that this post can augment the well-done docs that exist already by adding my own thoughts on this.

Run R Scripts in Power BI Desktop

Using R in Query Editor

Create Power BI Visuals Using R

You can also use R-based custom visuals in Power BI Desktop. However, you use those like any other visual rather than supplying your own R script. Since the focus here is on using your own R script, I decided not to discuss R-based custom visuals in this post.

Note on the Format of This Post

As you no doubt follow my blog with rapt joy, you will know that I usually walk through step-by-step instructions with numbered lines of text like the following:

1. Do this thing. It should look like Figure A.

Figure A

2. Now do this other thing. It should look like Figure B.

Figure B

For this post, I decided to start using the numbered stamps available in SnagIt (I LOVE THIS TOOL) so that the images themselves will walk through the steps as much possible. To that end, as you read this post, I would love to get your feedback on how this works for you, dear reader. If you feel strongly, or don’t, I would appreciate your thoughts in the comments section below. Thanks.

Prerequisites

All of the options I will show require you to have R installed on your machine. I am using R version 3.4.3 I got here as well as R Studio (an IDE: Integrated Scripting Environment) version 1.1.383 I obtained here. You can also use Microsoft R Open, which you can get here. All are free. I am choosing base R and R Studio because I want to play with/show the use of non-Microsoft tools in conjunction with Microsoft tools. I am using 2.53.4954.481 64-bit (December 2017) of Power BI Desktop. Note that things could look/behave differently in other version of Power BI Desktop.

For this post, I am using a well-known dataset known as the Iris dataset, which you can read about here. I downloaded the zip file from here to obtain a csv file of the data set for one of my examples. The Iris dataset is also included in the “datasets” package in R Studio, which I will use as well.

Note: A key R concept to understand is that of a data frame, which is essentially just data in a tabular format. In a data frame, the “columns” are actually called “variables.”

Once you have R and an R IDE installed, Power BI Desktop will detect them. You can see this in the Power BI Desktop Options. You can follow numbered steps in Figures 1 and 2 below.

Figure 1

image

Figure 2

image

If you have multiple versions of R or multiple R IDEs installed, you can choose which ones you want Power BI Desktop to use via the drop-down menus shown in the red rectangles in Figure 2 above. You can also use this window to reach further documentation and change what location R custom visuals use for temporary data storage.

Query Editor – Source

When you use R as a Source, both the Import of the data and (optionally) and Transformations on that data are performed by R. This can be a great option when you have R scripts that already prepare data the way you want and you want to mix that with other sources in Power BI Desktop. It can also be a great option when you have R scripts that access data sources not directly supported by Power BI Desktop. Or maybe you just prefer R to the Query Editor experience. Whatever your reason, here’s how to do it.

Figure 3

image

Figure 4

image

This opens the window in Figure 5. Paste the R script here, then click OK.

Figure 5

image

The full R script for this is the following:

#import the datasets package to get the iris data
library(datasets)

#import the plyr package to use rename
library(plyr)

#import the stringr package to use str_to_title
library(stringr)

#import the iris data into a data frame called IrisSource
#and rename the variables using the rename function from plyr
IrisSource <- rename(iris
, replace = c(
"Sepal.Length"="Sepal Length"
, "Sepal.Width"="Sepal Width"
, "Petal.Length"="Petal Length"
, "Petal.Width"="Petal Width"
))

#set the Species values to title case
IrisSource$Species=str_to_title(IrisSource$Species,locale = "en")

This script imports the Iris dataset from the “datasets” package that comes with R Studio and uses functions from a few other packages to rename variables and change the case of the values in the Species variable. I chose to do this so that my R script actually performed some amount of transformation in addition to importing.

Figure 6 shows the preview of the IrisSource table. To use it, you can click the checkbox to select it and click Load.

Figure 6

image

Figure 7

image

Once it loads, you should see the IrisSource table in the FIELDS pane in Power BI Desktop. Now you can use it like any other table (create DAX Measures, relate it to other tables, or even perform additional transformations in Query Editor if you wish). In this particular example, I did everything in the R Script to show that you can.

Query Editor – Transform

You also have the ability to use an R script to transform data you already imported into Power BI Desktop. This can be a great option when you already have data in your model and you want to shape it with R. In this example, we’ll import the Iris dataset from a csv file using the native source in the Query Editor. Then, we’ll use an R script to Transform it. Follow these steps to get this done.

Figure 8

image

Browse to the file you want to import.

Figure 9

image

Figure 10

image

Once you have imported the file, and are in the Query Editor, your screen should resemble Figure 11.

Figure 11

image

Figure 12

image

Note the highlighted line at the top of the Script box in Figure 12. This is an indication that the data in the table has already been imported into a data frame called “dataset” for use in your R script. So, you need to reference “dataset” in your script to work with the data. The full script for this example is below. It performs the exact same transformations as in the example above, this time naming the resulting table IrisTransform instead of IrisSource.

#import the plyr package to use rename
library(plyr)

#import the stringr package to use str_to_title
library(stringr)

#import the iris data into a data frame called IrisSource
#and rename the variables using the rename function from plyr
IrisTransform <- rename(iris
, replace = c(
"Sepal.Length"="Sepal Length"
, "Sepal.Width"="Sepal Width"
, "Petal.Length"="Petal Length"
, "Petal.Width"="Petal Width"
))

#set the Species values to title case
IrisTransform$Species=str_to_title(IrisTransform$Species,locale = "en")

Figure 13

image

In the interest of data privacy, it is necessary to tell Power BI Desktop what level of privacy is required for each source in the file here.

Figure 14

image

This is where you indicate how private each data source should be considered. You can read more about privacy levels here. This is particularly important in cases where you could be mixing, for example, data from a sensitive database with reference data from a public source. Since, in some situations, the Query Editor will include data from one source when filtering another, this could mean that data from a public source gets sent as part of a query to that sensitive database. This could compromise the security of the private database. Note that you can also choose to ignore Privacy Levels for this file. If you are using data sources that you know and trust, you can disable the check in order to increase query performance. Please be careful when choosing this option so that you don’t inadvertently throw away privacy checks that really should be in place. Here, I will just set both sources as Organizational.

Figure 15

image

Note that in the Properties pane at the right, I changed the name of the final Query to be IrisTransform. Once you Close and Apply, you should see that IrisTransform has been added to the data model.

Figure 16

image

Once again, this new table can be treated like any other table in your model.

Report View – Using Power BI Desktop Data Model

In Power BI Desktop, you have the ability to create R visuals on data already imported into the model. In this example, we will use an R script to create an R visual on the data in the IrisTransformation table we created in the previous example. Here’s how.

Figure 17

image

In order to use script visuals in your file, you must Enable that feature.

Figure 18

image

Figure 19

image

In the context of the R visual, we need to add the fields we want to use in the visual to the Values field. It is important for this example that you select the fields in the order they are listed in Figure 19. This is because the R script I got from the matplot documentation uses positional references instead of actual variable names when referencing the variables. Since Sepal Length is the first variable in the Iris data frame, that column must be selected first. This matters here because Power BI Desktop automatically lists fields alphabetically in the FIELDS pane. Rather than “fix” it myself and make the script use names instead of variable ordinal position, I decided to leave it alone to show that this is something you may have to deal with. Personally, I would avoid using ordinals as issues like the above can crop up. TSQL, likewise, supports using column ordinals in, for example, the ORDER BY clause. I never use that. Despite the extra coding involved, in the interest of clarity, I always use column name references.

Note that you MUST select at least one field from the FIELDS pane before Power BI Desktop allows you to paste in your R script.

Figure 20

image

The fields from the table you add to the Visual go into the data frame called “dataset” and become available to the R script. Once you have pasted your script, you can click the Run Script button (step 5 above) to execute it. The full R script for this example is as follows.

iris <- dataset

table(iris$Species) # is data.frame with 'Species' factor
iS <- iris$Species == "Setosa"
iV <- iris$Species == "Versicolor"
op <- par(bg = "bisque")
matplot(c(1, 8), c(0, 4.5), type = "n", xlab = "Length", ylab = "Width",
main = "Petal and Sepal Dimensions in Iris Blossoms")
matpoints(iris[iS,c(1,3)], iris[iS,c(2,4)], pch = "sS", col = c(2,4))
matpoints(iris[iV,c(1,3)], iris[iV,c(2,4)], pch = "vV", col = c(2,4))
legend(1, 4, c(" Setosa Petals", " Setosa Sepals",
"Versicolor Petals", "Versicolor Sepals"),
pch = "sSvV", col = rep(c(2,4), 2))

The script above is a slightly modified version of an example from the documentation for matplot here. My modification was to import the “dataset” data frame into one called “iris” that is used throughout the script. That meant I didn’t have to change all the references in the script I got from the matplot documentation example. I should note that I do not fully understand how the script works yet. I chose it because it produces a very interesting visual that is very clearly NOT one natively produced by Power BI. The final visual is shown in Figure 21.

Figure 21

image

Note that Power BI Desktop lists the columns involved in the visual in the title bar, just as it does with native visuals. In this case, it lists out all of the columns from the IrisTransform table in the order I selected them.

Report View – Bypassing Power BI Desktop Data Model

With this method, the script use for the R visual references data outside of the Power BI Desktop data model.

Figure 22

image

Figure 23

image

We select one field in order to activate the R script pane in the R visual.

Figure 24

image

The full script is as follows.

table(iris$Species) # is data.frame with 'Species' factor
iS <- iris$Species == "Setosa"
iV <- iris$Species == "Versicolor"
op <- par(bg = "bisque")
matplot(c(1, 8), c(0, 4.5), type =  "n", xlab = "Length", ylab = "Width",
        main = "Petal and Sepal Dimensions in Iris Blossoms")
matpoints(iris[iS,c(1,3)], iris[iS,c(2,4)], pch = "sS", col = c(2,4))
matpoints(iris[iV,c(1,3)], iris[iV,c(2,4)], pch = "vV", col = c(2,4))
legend(1, 4, c("    Setosa Petals", "    Setosa Sepals",
               "Versicolor Petals", "Versicolor Sepals"),
       pch = "sSvV", col = rep(c(2,4), 2))

 

In this case, I completely ignore the “dataset” data frame provided by Power BI Desktop. Instead, I reference the iris dataset that R Studio already knows about.

Figure 25

image

As evidence I am not cheating, notice that my “dataset” data frame only includes the Species field from my data model. Yet the visual still properly plots the length and width just like my previous example. Also notice the title bar of the visual indicates the one field from IrisTransform I selected, but did not use.

There is one important thing to note when choosing whether to bother importing data into the Power BI Desktop data or just have the R visual take care of it. Visuals that do not reference data in the model will not participate in cross filtering and slicers. So, if we put the visuals from the two examples side by side and add a slicer, you will notices that the visual based on the IrisTransform table in the data model is filtered by the slicer, but the visual from our final example, bypassing the data model, is not.

Figure 26

image

Notice when Setosa is selected in the Species slicer, the Versicolor data points disappear in the left visual (using IrisTransform), but not the right(bypassing the Power BI Desktop data model).

Wrapping Up

Power BI Desktop is already a super flexible tool with the native functionality. The ability to extend that with R scripts just adds another layer of awesome. Hopefully, I showed that it is not very hard to use R scripts in Power BI Desktop. As I continue learning R, I hope to produce more posts that look even deeper at this great functionality.

Comments

Pingback from Running R Scripts In Power BI – Curated SQL
Time December 22, 2017 at 7:10 am

[…] Mark Vaillancourt shows how to run an R script inside Power BI Desktop: […]

Comment from Tristan Malherbe
Time December 27, 2017 at 9:32 am

Great one ! The integration of R within Power BI makes both products even more powerful.
I look forward to Python in Power BI !

Comment from Mark V
Time December 27, 2017 at 9:38 am

Glad you found this valuable, Tristan. Python will be cool… Yet another thing on my “Learn All The Things” list…

Comment from situs judi terpercaya
Time April 24, 2018 at 4:51 am

Superb blolg you have here but I was wantring to know if yyou knew of any
forumss that cover the same topics discussed in this article?
I’d really love to be a part of online community where I
can get feedback from other knowledgeable people that share the same interest.
If you have any suggestions, please let mme
know. Appreciate it!

Comment from Mark V
Time June 7, 2018 at 9:22 am

Hi, Situs. Sorry for the delayed response. Are you asking about Power BI, R, or both? For Power BI, you can head over to community.powerbi.com and sign up. Lots of great interactions there with other Power BI Users. I’m not sure what online communities are best for R, but if you follow the #rstats hashtag on Twitter, you could likely get pointed in the right direction.

Comment from Rolfmao
Time June 14, 2018 at 5:23 am

One of the best how to’s for using R scripts in Power BI

Comment from Belle
Time February 2, 2019 at 11:48 pm

Very good post! We are linking to this great article on our website.
Keep up the good writing.

Comment from furnished office
Time June 17, 2019 at 11:42 am

Hello to every one, because I am really eager of reading
this blog’s post to be updated on a regular basis.
It includes nice data.

Comment from Jeanette
Time November 22, 2019 at 3:13 am

For those of you who like to perform gambling and when you are looking for partners within gambling
activities, Mabosbet is usually the right choice. Mabosbetransfer You can contact our customer service
and make transactions with us. Mabosbet livechat is available 24 hours nonstop and
is ready to serve you and help you online with betting.

Mabosbet. com as a responsible gambling agent,
we always provide an option link to mabosbet to ensure members feel comfortable.
You no longer need to have trouble finding an alternative solution Jababbet
link with regard to access, it is currently available for you.

We also always give concern to the safety associated with our members, very strict protecting all the privacy of members of Jababbet.

Trusted Mabosbet From 2010

Along with good praise, the support from the Headquarters is usually no
doubt. Friendly, quickly response, under 3 mins.

Mabosbet is highly appreciated by gambling bettors from Indonesia because whatever triumph you get will probably be approved by mabosvip.
We usually want to provide the particular
best for members with highest service. The site mabosmail.

com can also be very an easy task to access, when you only need internet access and alternative links mabosvip, no require to use proxies,
newsletters, or troublesome applications.

That will is why many men and women join mabosbola and make deposits, then,
win the match. The best and quality alternative mabosbola option site.

Come join mabosbet. com the trusted on the internet gambling agent in Indonesia.
Contact livechat mabosbet now in the lower correct corner to get
the user ID. Welcome to be able to the club.

MABOSBET is a Trusted Online Wagering Site that has recently been established for more than 9 years with
typically the greatest amount of online gambling players. This gambling
web site also provides the most satisfactory online gambling game
together with the most popular gambling providers
in Indonesia. On the internet Gambling Games that
may be played at MABOSBET are Sportsbook Soccer
Gambling, Live Casino, On-line poker Card Gambling, Online Slot Gambling and Online Togel.
Mabosbet itself has provided a number of excellent facilities for gambling online
players in Indonesia ranging from 24-hour Live Chat Assistance, Bonuses with the least
difficult conditions, additional wins regarding True Gambling Players.

This specific MABOSBET Gambling Site furthermore
proves to get very trustworthy, while this Gambling
Internet site can also pay with regard to any winning player without deductions.
So you may play gambling comfortably in addition to safely without fear of spending.
In addition to the definite win at MABOSBET, you will also offer additional bonuses
to supply a different experience actively playing online gambling.

JOBI BALL OFFICIAL SBOBET ONLINE IN INDONESIA
Online Soccer Wagering has become a very popular internet
gambling game inside Indonesia where every online game has a market regarding betting.
MABOSBET is now a good official partner of SBOBET Indonesia, where more than a
decade has provided soccer gambling games for gambling players in Indonesia.
This specific SBOBET Soccer Gambling Marketplace allows a reasonable where gamers
will actually be provided with a live market from your recognized match directly.
So you may be very lucky to be able to play
SBOBET in this specific MABOSBET.

RELIABLE ONLINE SLOT MACHINE GAME SITE SITE WITH
MANY PROFITS
Online slot gambling is currently popular in Philippines,
where online slots are really lucrative with huge jackpots and freespins that are simple to win. Because
associated with that this MABOSBET first provided Online Slot Games from 2012 for gambling players in Indonesia.
Where Jackpot is provided a lot more than USD $ 1, 000, 000 for slot machine players who register
at MABOSBET. The Slot Sport machine at MABOSBET
doesn’t just use your Stability, the Slot Game provides been made with the FREESPIN Bonus that comes out more often, so a
person will be Fortunately.

Comment from sbobet
Time December 14, 2019 at 2:14 am

Sbobet is a trusted gambling site that operates to foster every fans of online gambling in the world.
Founded in 2010, it didn’t agree to long
for Sbobet to become the biggest online site in Asia and subsequent to it comes to the amount of betting child support that rotates all
week, Sbobet is as a consequence one of the biggest
sites in the world.

Sbobet is unconditionally popular, especially accompanied by Asian bettors,
the defense is that the site has been awarded as the best
gambling operator for two years in a row. It offers a variant of football betting gone
extremely low margins in the same way as compared to other sites in its
class.

Comment from l69
Time December 22, 2019 at 6:55 am

Option Link l69
L69 is one of the official sites that supply the newest
alternative link trustworthy online betting sites.
L69 provides more than one alternative link from each and every betting site, and we
all here have become probably the most comprehensive alternative link
companies and the L69 web site includes a responsive display
of which makes it easy that you can access our site coming from the display of your current device or laptop.
Plus what you should know is that we here just have 1 official link, l69.
myself and if there are other websites that offer information or call our website is
not the official site.
Ball wagering and casino gambling are forms of games that are very popular with
the folks of Indonesia. Why l69? Since we have been here for
more than five years and have got become the trust
regarding the people of Indonesia as a spot to perform soccer gambling and also online casino gambling.
Itself in addition has obtained a license from the Asian and Western european governments in 2009 to become an online gambling
gambling provider site in Asian countries
and Europe. By offering many different types of wagering bets that may be played upon one ID, much more the particular members
feel happy enjoying in your bookie.
Benefits of Alternative Links l69
But unfortunately lately there are usually many members who experience
problems and problems when entering into their Sgd777 game for the reason that Sbobet site is frequently blocked and lots of aged sites have been blocked by the Indonesian authorities rather than a few are usually also affected with what will
be called the Newsletter Job. The Indonesian government lately is struggling to eliminate
links or paths to get into sites that smell regarding pornography, gambling and presently there are many other content
that will is deemed inappropriate.

Comment from Antoinette
Time December 30, 2019 at 9:41 pm

Not only gives soccer gambling and sports betting games, Betberry will be also one of the most trusted
Joker123 providers in Indonesia that is chosen by millions of betting fans every
day. Using the Trusted Joker123 Agent, you are able to enjoy hundreds of distinctive games
from the casino, slot and game classes that are currently
extremely popular with players on the planet, namely shooting fish.

You must know that Joker123 or Joker Gaming is one regarding the biggest online betting shops in Asia,
that has served millions of players together with international
standard games and operates under official
permits. On the site joker123 also provides various types of casino gambling
and slot games, as well as fish shooting or perhaps
fish hunter games that are their mainstay games.

Not only a complete game, Joker123 also arrives with attractive features including the following:

Live casino at redbet Joker123
Regarding those of you that want to feel a new new sensation in actively playing casino gambling, with the particular
official Joker123 agent Betberry you can play gambling live!

This implies you will end up being playing in a real-time casino
gambling system plus served by beautiful dealers that you can interact with.

Not only the fun of playing casino gambling, yet here
you also acquire the atmosphere like playing in a gambling house directly in Vegas!

Joker123 Online Slots
Many folks choose joker123 slot wagering as the game is not hard
and doesn’t require a lots of effort like casino video games or soccer betting.
But a game won’t end up being fun if this doesn’t consist of challenges, right?
At the Trusted Joker123 Agent a person can challenge yourself in order to reach the
biggest intensifying jackpot the location where the number regarding jackpots will carry on and boost whenever until the participant
can get it.
Shoot the Joker123 fish
This particular one is really a fairly fresh game but has previously attracted the attention of millions of gambling players all over the world.
Feel the fascinating sensation of playing taking pictures fish with abundant
prizes. In this game a person will not play by yourself as this
game can become played by up to be able to 8 players simultaneously.
Show your skills and attain maximum victory in typically the game Fish Hunter Joker123.

Comment from www.mabosvippro.Com
Time January 9, 2020 at 8:23 am

For anyone who like to enjoy gambling and if you are usually looking for partners
within gambling activities, Mabosbet is the right choice.
Mabosbetransfer You can contact our customer support and make transactions with us.
Mabosbet livechat is available 24 hrs nonstop and is all set
to serve you and help you online with gambling.

Mabosbet. com as a responsible gambling agent, we always provide an alternative link to mabosbet to ensure that members feel comfortable.

You no longer need to have trouble finding an alternate
Jababbet link regarding access, it is currently available for
you.

All of us also always give concern to the safety of our members,
very strict protecting all of the privacy regarding members of Jababbet.

Trusted Mabosbet From 2010

Together with good praise, the support from the Headquarters is no doubt.
Friendly, quickly response, under 3 moments.

Mabosbet is highly valued by gambling bettors through Indonesia because whatever victory you get will probably be recognized by mabosvip.
We constantly want to provide the best for members with optimum service.
The site mabosmail. com is also very straightforward to access, as you
only require internet access and alternate links mabosvip, no
need to use proxies, notifications, or troublesome applications.

That is why many individuals join mabosbola and create deposits,
then, win the match. The best plus quality alternative mabosbola option site.

Come join mabosbet. com the trusted on the internet gambling agent in Philippines.
Contact livechat mabosbet now in the lower proper corner to get the user ID.
Welcome in order to the club.

MABOSBET is a Trusted Online Gambling Site that has already been established for more than 9 years with
typically the biggest number of online wagering
players. This gambling web site also provides the most
satisfactory online gambling game with the most popular
gambling providers in Indonesia. On the internet
Gambling Games that may be played at MABOSBET are Sportsbook
Soccer Betting, Live Casino, On-line poker Credit card Gambling, Online Slot
Betting and Online Togel. Mabosbet itself has provided a
number of excellent facilities for internet gambling players
in Indonesia which range from 24-hour Live Chat Support, Bonuses with the least
difficult conditions, additional wins for True Gambling Players.

This specific MABOSBET Gambling Site also proves to get very trustworthy,
while this Gambling Site can also pay with regard to any winning player without the deductions.

So you will certainly play gambling comfortably and safely without
anxiety about paying. In addition to the definite win at MABOSBET, you will also provide additional bonuses to provide a different experience enjoying online gambling.

JOBI BALL OFFICIAL SBOBET ONLINE INSIDE INDONESIA
Online Soccer Wagering has become a very popular online gambling game within Indonesia where
every online game has a market with regard
to betting. MABOSBET is becoming an official partner of SGD777 Indonesia, where more than six
years has provided sports gambling games for betting
players in Indonesia. This specific SBOBET Soccer Gambling
Industry allows a good where players will actually be given a live market from
your official match directly. Which means you will certainly be very lucky
to be able to play SBOBET in this MABOSBET.

RELIABLE ONLINE SLOT SITE SITE NUMEROUS EARNINGS
Online slot gambling will be currently popular in Dalam negri, where online slots are really rewarding with
huge jackpots and freespins that are usually an easy task to win. Because of that this MABOSBET
first provided Online Slot Games from 2012 for wagering players in Indonesia.

Wherever Jackpot is provided a lot more than USD $ just one, 000, 000 for slot machine
players who register at MABOSBET. The Slot Game machine at MABOSBET
won’t just use your Balance, the Slot Game provides been designed with the FREESPIN Bonus that comes out there
more often, so an individual will probably be Fortunately.

Write a comment