R in Power BI

❓ Why R in Power BI?

Microsoft Power BI supports two different languages, M language and DAX (Data Analysis Expression).

However, it is sometimes more convenient to use R for the data preparation process since R has an R package, a set of useful functions that eliminate the need for writing codes from scratch. Moreover, data analysts who are familiar with R can easily pick up R to apply in Power BI for the web scraping or EDA (Exploratory Data Analysis) process.

Moreover, some visualizations require some R packages. You can create and adjust these visualizations without little programming knowledge (just knowing how to install R packages is enough and you can learn how to do it in this article)

Support R packages can be found in the below link.

เรียนรู้แพคเกจ R อันไหนที่ได้รับการสนับสนุน - Power BI

If you are a Python lover, this article is for you.

Python in Power BI

Contents in this article

• Install R
• R setting in Power BI
• Use R to get data
• Use R in transformation
• Use R to visualize
• Use visualizations that require R packages

📥 Install R

Before using R in Power BI, you have to install R. An easy way is to download it from https://cran.r-project.org/bin/windows/base/

After downloading complete, install R on your machine.

Then, open R

You will see something like this

You can type code behind the red arrow and press Enter to run the code.

This is only a brief introduction to R so that you can install a required R package for Power BI visualization.

Install R packages

Some visualizations in Power BI require some R packages, so it is useful to learn how to install R packages.

You can install the R packages that you will use in Power BI by typing the following code.
(Replace <package_name> with the name of the package you want to install such as ggplot2, corrplot)

install.packages("<package_name>")

⚙️ R setting in Power BI

After completion of R installation, it is time for Power BI!!

1. Open Power BI.
2. Click File in the upper right corner.

3. Click Option and setting. Click Option.

4. Click R scripting

5. Check that the Detected R home directory points to your installed R. If not, change the Detected R home directory to Other and browse for your installed R location.

Now, you are ready to use R in Power BI

R can be applied in at least 3 steps: Get data, Transformation, and Visualization. I also include how to use existing visualization that require R package.

🧺 Use R to get Data

R can be used to get data. This is helpful in web scraping (getting data from the website).

In this example, I will create an R file to get data from 2 sources: a csv file and a created table (a table with rows and columns).

1. Click Get data

2. Search for an R script and click to open a new coding window.

3. Type the below code and click ok.

df1 <- read.csv(file = 'C:/data.csv')
df2 <- data.frame(player = c('AJ', 'Bob', 'Chad', 'Dan'),
                 position = c('A', 'B', 'B', 'B'),
                 points = c(1, 2, 2, 1))

4. You will see a Navigator plain to show data (like in other get data methods.). Select data to load.

🔄 Use R in transformation

R can be used in the data transformation step as well. Mostly, I use it for the regular expression to extract sub-strings from another column that match defined patterns such as getting hashtags from Twitter texts.

In this example, I will find text length using nchar() function in R.

1. Click Transform data

2. Select the query you want to transform

3. In the Transform tab, click Run R script

4. You will see a new Run R script window. Write your code here and click ok.

I use R to find the length of text in the column ‘OriginalTweet’ and create a new data called ‘new_dataset’

Key concept: the data will be a ‘dataset’ table, so you can manipulate it with table functions. I found it easier to copy data to a new table and transform that table instead.

5. The result will be a table. Click to expand the table. Make sure that ‘Use original column name as prefix’ is not checked.

Note: The new_dataset table may expand automatically without this step

6. You will see the result of data transformation and the steps are added in APPLIED STEPS plain.

After this, you can use this new_dataset to create a dashboard.

📊 Use R to visualize

Creating a plot using visualization from Power BI alone may have some limitations and some plots may not available in Power BI.

R will come in handy since there are many R packages that can generate any visualization as you wish.

R packages for Data Visualization that are commonly used these days include ggplot2, Lattice, and Plotly

Although writing codes in R to create a plot may be more difficult than the drag-and-drop concept of Power BI, there are much more plot customization and code examples (to copy).

1. In the Visualization plain, click the R icon.

2. You will see an empty R script editor area. Select the columns you want to visualize.

3. You will see an empty coding area. Write code and click the run icon and wait for the result. Done!!

Note: If it shows an error after running the code, take a deep breath and read the error message.

📂 Use visualizations that require R packages

This method lay in the middle between hard coding and the drag-and-drop concept.

You can create some complex graphs which require an R package while you don’t need to code yourself and you can adjust the graph in a user-friendly manner. This visual can be downloaded at the marketplace.

In this example, I will create a correlation plot.

In the Visualizations plain on the right-hand side, click at 3 dots and select “Get more visuals”

Visualizations plain >3 dots > “Get more visuals”

This will open the visual marketplace. Search for the Correlation plot using the search area on the right-hand side. Click on the Correlation plot (please make sure that it is the same one as the below image). Then, click Add.

Search Correlation plot > Click Correlation plot > Add

Pay attention to R package and version.
This plot require ‘corrplot’ package to be installed. I will talk about it in a few minute.

Now, you will see the Correlation plot icon in the Visualizations plain.

Click on the Correlation plot icon in the Visualizations plain.

You will see an empty visualization.

Empty visualization

In the Fields plain on the right-hand side, select columns in which you want to create a Correlation plot. I will select the Count and UserName columns as an example (it does not make sense to find a correlation between these columns. This is just an illustration only)

You may see something like this if you don’t install the required R package. When you click on ‘See detail’, it will tell you that there is an error because package ‘corrplot’ is not installed.

Error if you don’t install the required R package

To solve this issue, install the ‘corrplot’ package (read how to install R packages at the top of this article).

After installing the ‘corrplot’ package, the correlation plot will be created.

You can custom the graph in the Format your visual icon in the Visualization plain without writing any line of code.

This is how to use existing visuals in the market that require R packages. The only thing you need to know is how to install R packages.

Conclusion

This article shows how to apply R in Power BI step by step, so you can get the advantage of both the Power BI interactive dashboard and R flexibility. You can apply R in many ways including data acquisition, transformation, and visualization.

Hope you get some idea about using R in Power BI.

Please clap and follow me if you want to read more data analytics content.

Mlearning.ai Submission Suggestions