R For Power BI A Beginner's Guide - Enterprise DNA

R For Power BI | A Beginner’s Guide

No comments

In this tutorial, I’m going to demonstrate the 3 different ways to utilize R for Power BI. This includes importing data into Power BI, applying steps in Power Query, and creating a data visualization.

Setting up R for Power BI will allow you to perform certain tasks such as the ones that were mentioned above. Some of these are similar to how we integrated Python in Power BI.

Configuring R For Power BI

Once you open Power BI, you should see a blank report. To integrate R in Power BI, first, go to the File menu.

R for Power BI

Then click on Options and settings. After that, select Options.

R for Power BI

When you select Options, a pop-up box will appear. Under the Global group of options, click on the R scripting option. After that, you should see options to assign R home directories and R IDEs.

R for Power BI

If you don’t see this option, you can simply click on the dropdown and select Other.

R for Power BI

After that, a new input box will appear where you can set the R home directory manually.

R for Power BI

To properly identify the path for the R home directory, you need to open RStudio and click on the Tools menu. Then select Global Options.

R for Power BI

This is where you can check the file path where your R is installed. In my case, this is the file path where my R is installed.

R for Power BI

This was the file path that you saw earlier in the previous examples, which means I need to update my version from R-4.0.2 into R-4.1.1.

On the other hand, you can just copy the file path that you can find here and paste it in Power BI to set the R home directory.

I’ll select the file path to update my version into R-4.1.1.

R for Power BI

For the Detected R IDEs, select the R Studio option. This will allow us to work with data in R Studio and take it to Power BI. Then click on OK to finish setting up R for Power BI.

R for Power BI

3 Different Ways To Utilize R For Power BI

Let’s begin with importing data using the R script in Power BI followed by applying steps in the Power Query. Then, we’ll create a visualization for the data that we’ll be creating.

1. Importing Data Using R Script In Power BI

First, click on Get Data and select the More option.

R for Power BI

After that, a Get Data pop-up box will appear. Search for R script in the search box and choose R script from the results. Then click on Connect.

R for Power BI

This will open the R script dialog box.

R for Power BI

Once the R script dialog box is open, we can then begin importing data. For this example, we’ll import a CSV file using R script and turn it into a table.

R for Power BI

To import the CSV file, use the read.csv function and include the file path of the CSV file that you want to import inside the parentheses. In this example, I stored it in a variable named tips. To execute the script, just click OK.

Once the script is executed, a table named tips should be created as a result. In the Navigator pop-up box, you can see the preview of the table. Let’s click on Transform Data to open this table in Power Query.

R for Power BI

The table should look like this after clicking on Transform Data.

R for Power BI

2. Applying R Scripts In Power Query Editor

After opening the tips table in Power Query, this is now considered a dataset. From here, we can perform some functions to create a calculated column as an example, based on the dataset that we have. To do that, click on Transform and select Run R script.

An R script dialog box will appear and we can run another script to create a copy of the current dataset that we have. We’ll also create a new column named m_f with data coming from the sex column using the code below.

R for Power BI

The line new_dataset <- dataset means we’re putting the content of dataset in the new_dataset variable.

Then in creating the new column, I stored a substring function in the new_dataset$m_f variable. Notice that I used a $ symbol before m_f. This is the symbol used to create a new column using R script.

Inside the substring function, the code new_dataset$sex, 1 ,1 means that we’re only taking the first character of the sex column’s data. So instead of taking Male and Female, we’ll only take M and F. Click on OK to execute the code.

In the Applied Steps section, you can track the steps that you’ve executed in the current dataset you’re working on. Then if you scroll the table to the right, you should see the new m_f column along with the data from sex column.

R for Power BI

In case you want to go through what you did in the previous step, just click on the gear icon. Then it would open the Run R Script dialog box.

R for Power BI

Now that we’re done applying R scripts in our dataset, we can safely close and apply the changes that we’ve made.

R for Power BI

After doing that, it will load the tips table in Power BI.

R for Power BI

3. Building An R Visualization In Power BI

Once the tips table is loaded in Power BI, we can start building an R Visualization based on that table. Under the Visualizations pane, click on the R script visual option then click on Enable to proceed.

R for Power BI

You should see something like this after enabling the script visuals for R.

R for Power BI

To begin displaying data in our visualization, just head over to the Fields pane, expand the tips table, and select the tip and total_bill fields.

R for Power BI

In the R script editor, we’ll see some generated code after selecting the tip and total_bill fields. These codes are automatically generated and executed for creating a dataset or dataframe. They’re also for removing duplicate rows from the data that we’re trying to display in our visualization.

R for Power BI

After that, we can now begin plotting data based on our selected fields. For this example, I’m going to create a scatter plot for tip and total_bill using the plot function.

Using the plot function, we can include dataset$total_bill and dataset$tip in the arguments to specify the columns to be displayed in our visualization. To execute this script, just click on the play icon in the upper right corner of the R script editor.

After that, we’ll now see the scatter plot for tip and total_bill in the preview area.

R for Power BI

Alternative Way Of Editing A Plot Using RStudio

Depending on the plot that you want to create, you might need to go directly to RStudio and apply the steps to create a plot and go back to Power BI.

Then copy and paste the codes that you did in RStudio. This is due to the lack of RStudio features while editing in Power BI’s R script editor.

In fact, we can move the R scripts that we’ve created here in Power BI into RStudio. You can do this by clicking on the diagonal arrow on the upper right side of the R script editor.

This is what will happen after clicking on the diagonal arrow in the R script editor in Power BI.

Once the script is moved into RStudio, we need to select all of the codes and execute them to see the output.

You can make any changes here in RStudio and bring everything back to Power BI once you’re done editing. For instance, I’m going to switch the variables and execute them to make some changes in our plot.

In this example, I moved the tip into the x axis and the total_bill into the y axis of the plot. Since we’re done applying changes, we can copy the code and paste it back into Power BI and apply the changes there.

In addition to visualization, this can change with the use of a slicer as we use it in our Power BI report. For reference, I’m going to add a slicer and check the time under the Fields pane.

In the slicer, we can click on the Lunch or Dinner options to change the data in our plot based on the selected time.

Take note that depending on how large the dataset that’s being visualized, you may experience some delay. Sometimes it might be slow when changing the plot’s output using a slicer.

***** Related Links *****
Upcoming Course: Basics Of R For Power BI Users (Part 1)
R And RStudio Download And Installation Guide
R Scripting For Power BI Using RStudio

Conclusion

Taking everything into account, you’ve learned how to integrate R in Power BI. You were also able to utilize R for Power BI in 3 different ways: data importing, applying R scripts, and building an R visual.

Furthermore, you were able to utilize various symbols and functions to perform certain tasks and visualizations. Keep in mind that you can edit your R scripts in RStudio and bring the changes back into Power BI.

George

Enterprise DNA Power BI On-Demand

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.