Power BI & R – Dependent Scripts

A quick blog post this week. I want to share a trick that I learned at the Microsoft Business Applications Summit for using an R script as a data source for a Power BI report. I heard this at the session: “Getting Started with R in Power BI”.

A bit of background: I typically write my R code in R Studio. Below is an example of some code that I used in my “R Visuals in Power BI” session. I demoed how to use R code as a data source in Power BI.

If I wanted to include to use this code in a Power BI report, I would go to the R script data connector. This is found in the Other category in the Get Data window.

Then I would copy and paste the R code I had written in R Studio into the R script window in Power BI. This would then be loaded to the Power Query Editor.

This process would work fine; however, there is a slight problem when I update my code in R Studio, I had to remember to re-copy and paste into Power BI.

But no more!

The trick I learned was to use the source() function in the R script window to reference the .R file that contains my code. This connects to my R code directly and as a result, any updates I make in R Studio are reflected in my Power BI report.

I tried this out using the same example data and it gave me the same results. This is a brilliant trick and allows for a more natural integration of my work with less manual input!

I’ve started working more with some new technologies for me lately: Microsoft Machine Learning Studio, Flow and Cognitive Services. I’m playing around with the R integration into these platforms and how it can connect to Power BI.

I’ll be tweeting about my progress on @FthrsAnalytics and I’m hoping to have some content on these topics up over the next few months. Be sure to keep an eye out!

Leave a Reply

Your email address will not be published. Required fields are marked *