Using R Programming on Power BI Desktop

Image by Author

Power BI and R are two giants in the world of data but with different use cases.

While Power BI is used in the field of Business Intelligence to build reports and interactive dashboards, R is a programming language for statistical analysis and data science.

In this post, I am going to show you how you can have the best of both worlds by running your R Scripts in Power BI.

Please ensure you have Power BI Desktop and R installed on your PC before proceeding.

Importing Data

First of all, you need to get Power BI Desktop up and running and also ensure you run Power BI as an administrator.

Image by Author

You are going to be having a screen like the one shown above.

I am going to be using the motorcycle sales data for demonstration, you can download it here.

Or you can feel free to import your own datasets.

Click on Get data from another source -> Text/CSV -> Connect.

Image by Author

You should have something like this

Image by Author

Let’s transform the data by clicking on Transform Data.

This will open the Power Query Editor.

Image by Author

This is where messy data gets cleaned in Power BI, just like you taking a shower after your morning gym session, to avoid stinking the whole office like someone who just came back from a war with zombies.

Running R Scripts in the Power Query Editor

In this window, you have two options, either you clean your data like momma’s boy or you do what real men do.

Image by Author

I am going to go for the latter, go to the transform tab on the ribbon, click on the option Run R Scripts

After clicking, a space to write your R scripts will be provided to you

I am going to run the R script below which imports the dataset and filters only retail clients.

The key takeaways here are:

• The data to be imported has to be called dataset.

• The new data which is a transformation of the imported data has to be named output.

Upon clicking okay, you will get information saying your Information is required about your data privacy.

Image by Author

Click on continue, check on ignore privacy levels then save.

Image by Author

Voila!!

Image by Author

Close and apply this transformation so as to visualize the data in Power BI

Image by Author

Image by Author

Running R Scripts for Data Visualization

To create a visualization with an R script, click on the R script visual on the Visualizations pane.

Image by Author

You will be asked to drag the fields you will be needing in your visualization to the values area

Image by Author

I want to visualize the total amount made at each warehouse. I will be dragging the warehouse and total columns.

Image by Author

Your data has already been created by Power BI as a dataset, yours is to just provide the script that will visualize your data.

Run the script below,

library(tidyverse)
dataset %>%
    group_by(warehouse) %>%
    summarize(total = sum(total)) %>%
    ggplot(aes(x = warehouse, y = total, fill =  warehouse)) +
    geom_col()

Image by Author

You can create more visualizations and create a report without running any DAX query.

Conclusion

Sometimes you might want to create complex visualizations, which is easier to do on R.

This integration gives you room to build such visualizations.

Thanks for reading, hope you find the post insightful.