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.
Then click on Options and settings. After that, select Options.
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.
If you don’t see this option, you can simply click on the dropdown and select Other.
After that, a new input box will appear where you can set the R home directory manually.
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.
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.
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.
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.
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.
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.
This will open the R script dialog box.
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.
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.
The table should look like this after clicking on Transform Data.
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.
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.
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.
Now that we’re done applying R scripts in our dataset, we can safely close and apply the changes that we’ve made.
After doing that, it will load the tips table in 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.
You should see something like this after enabling the script visuals for R.
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.
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.
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.
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
