How to Run R Script in Power BI

Power BI is incredibly powerful on its own, but sometimes you need to perform data transformations or statistical analyses that go beyond its built-in capabilities. This is where R, the popular open-source language for statistical computing, comes in handy. This tutorial will walk you through exactly how to set up and run R scripts directly within Power BI to clean, transform, and visualize your data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use R with Power BI?

Integrating R into your Power BI workflow opens up a whole new world of analytical possibilities. While Power BI's DAX and M languages are excellent for many business intelligence tasks, R excels in specific areas that can take your reports to the next level.

• Advanced Statistical Analysis: Run complex statistical models, like cohort analyses, linear regressions, or time-series forecasting directly on your data.
• Complex Data Transformation: Handle sophisticated data cleaning and manipulation tasks, such as imputing missing values using advanced algorithms or restructuring messy datasets in ways that are difficult with Power Query alone.
• Custom Visualizations: Leverage R's extensive library of visualization packages (like ggplot2) to create highly customized and publication-quality charts that aren't available as standard Power BI visuals.

Step 1: Setting Up Your Environment

Before you can run a single line of R code in Power BI, you need to make sure your computer is properly configured. This involves installing R and telling Power BI where to find it.

Install R and RStudio

First, you need a local installation of R. Power BI doesn't come with R built-in, it simply connects to the version you have on your machine.

1. Download R: Go to the Comprehensive R Archive Network (CRAN) and download the correct version for your operating system (Windows, Mac, or Linux). Follow the installation prompts.
2. Download RStudio (Recommended): While not strictly required by Power BI, RStudio is a free and user-friendly Integrated Development Environment (IDE) for R. It makes writing, testing, and debugging your scripts much easier before you bring them into Power BI. You can download it from the Posit website.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Configure Power BI to Recognize R

Once R is installed, you need to point Power BI Desktop to its location. Power BI is usually pretty good at finding it automatically, but it's always smart to double-check.

1. Open Power BI Desktop.
2. Go to File > Options and settings > Options.
3. In the Options window, scroll down to the "Global" section and click on R scripting.
4. Under "Detected R home directories," ensure that a path is selected. If it found your installation, you’re all set.
5. If it’s blank or incorrect, select "Other" and browse to the home folder where you installed R (e.g., C:\Program Files\R\R-4.2.2).
6. Click OK to save the settings.

With that, your environment is ready. Now you can start using R inside Power BI.

Step 2: Running an R Script in Power Query Editor

One of the most common use cases for R in Power BI is data manipulation and transformation. You can do this directly in the Power Query Editor, using an R script as a step in your data cleaning process.

Let's walk through a simple example. Imagine you have a sales dataset with a "Revenue" column that contains some missing (NA) values. You want to replace these missing values with the average revenue of all other sales. This is a perfect task for R.

Step-by-Step Instructions

1. Get Your Data: Start by loading your dataset into Power BI. For this example, we'll assume you have a table with customer and revenue data.
2. Open Power Query Editor: In the Home ribbon, click on Transform data.
3. Run an R Script: Inside the Power Query Editor, go to the Transform tab in the ribbon. In the "Script" section, click on Run R script.

A new window will pop up titled "Run R script". You'll see a comment that says: # 'dataset' holds the input data for this script. This is critical. Power BI automatically takes the data from your previous Power Query step and transforms it into an R data frame called dataset. You will perform all your R operations on this dataset object.

1. Write Your R Script: In the script editor window, you will write the R code to impute the missing values.

# The 'dataset' object holds our input data
# Calculate the mean of the 'Revenue' column, ignoring NA values
mean_revenue <- mean(dataset$Revenue, na.rm = TRUE)

# Replace all NA values in the 'Revenue' column with the calculated mean
dataset$Revenue[is.na(dataset$Revenue)] <- mean_revenue

# The final line of the script implicitly returns the modified data frame
output <- dataset

1. Execute the Script: Click OK. Power BI will execute the R code on your data. You'll then see a new table named "output" appear. This is the new data frame you created. Click on "Table" next to its name to expand it and see your data with the missing revenue values now filled in.

You have now successfully used R to transform your data within Power Query! You can continue adding more transformation steps after this as needed.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Creating R Visuals in Power BI

Beyond data transformation, you can leverage R's powerful visualization libraries to create custom charts directly on your Power BI report canvas.

Let’s say you want to create a scatter plot of product price vs. units sold and add a linear regression line to see the trend. While Power BI has a trend line feature, using R's ggplot2 package gives you far more customization options.

Step-by-Step Instructions

1. Enable Script Visuals: Before adding your first visual, ensure script visuals are enabled in your Power BI settings as a security measure.
2. Add the R Visual Icon: On the report canvas, go to the Visualizations pane and click the R script visual icon (it looks like a small 'R'). This will add an R visual placeholder to your report and open an R script editor at the bottom of the screen.
3. Add Your Data Fields: Drag the data fields you want to analyze from the Fields pane into the "Values" well of the R visual. For our example, you would drag "Price" and "Units Sold".

Just like in Power Query, Power BI automatically creates an R data frame called dataset containing these fields.

1. Write Your Visualization Script: In the R script editor, you'll write the code to create the chart. This example requires the ggplot2 library. If you don't have it, open RStudio and run install.packages("ggplot2") once to install it.

# R automatically loads the 'ggplot2' library, 
# but it's good practice to call it explicitly
library(ggplot2)

# Create the scatter plot using ggplot
# We map 'Price' to the x-axis and 'Units Sold' to the y-axis
# The geom_point() function creates the scatter plot points
# The geom_smooth() function adds a regression line (method='lm')
ggplot(dataset, aes(x = Price, y = `Units Sold`)) +
  geom_point(color = "blue") +
  geom_smooth(method = 'lm', color = "red", se = FALSE) + 
  labs(title = "Price vs. Units Sold")

Note: If your column name has spaces, you need to wrap it in backticks ( ) in your R code, as shown with Units Sold.

1. Run the Script: After writing the code, click the "Run script" icon in the script editor bar. Seconds later, your custom ggplot2 visual will appear in the placeholder on your report canvas.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Tips for Success

As you start using R more frequently in Power BI, keep these tips in mind to avoid common issues:

• Keep Scripts Simple: R visuals have limitations on data size and execution time. They are best for visualizations, not heavy data processing. Do your heavy lifting in Power Query.
• Test in RStudio First: Always write and test your complex scripts in RStudio using a small sample of your data. This makes debugging much faster and easier than doing it inside Power BI's small script editor.
• Understand Privacy Levels: If your script combines data from multiple sources, you might run into errors related to Power BI's data privacy levels. You may need to adjust these in the options menu to allow scripts to run.
• One Visual at a Time: An R script visual can only produce one visual. You cannot generate two separate charts from a single script.

Final Thoughts

Integrating R into your Power BI reports adds a huge amount of flexibility for sophisticated statistical analysis and custom visualizations. Once you're comfortable with the initial setup, it becomes a straightforward process to call R scripts for either data transformation in Power Query or custom charts on your report canvas.

If you find that writing scripts and managing complex Power BI setups is pulling you away from actually getting insights, you're not alone. At Graphed, we’ve built a platform that removes this entire layer of technical complexity, letting you get straight to the answers. Instead of scripting, just connect your data sources and create live dashboards simply by describing what you want to see in plain English. This frees up your time to focus on strategy, not syntax and setup.