This week, I was putting together example files for my presentation: Predictive Analytics with R in Power BI.
One of the challenges I faced was how to make it easy to update the file paths of the source data in my Power BI report. The location of the source files would have a different file path for each person who downloaded the demo materials.
I knew I could achieve this by using a parameter in my connection strings and saving my report as a .pbit template file.
The Power BI report uses both an Excel workbook and .R files as data sources. I started with the Excel workbook.
The Source step in my query is:
= Excel.Workbook(File.Contents("C:/Users/josyeat/Desktop/R in PBI/Excel Source Data.xlsx"))
I needed to create a parameter that could replace everything before the “R in PBI” part of the connection string. Everything after this section would be the same for all users so I could leave it hard coded.
I created a text parameter that could take on any value. I set the current value to the location of the source files.
I could now reference this parameter in the connection string of all the source files. If these files change location, I only need to update the file path in one place!
I changed the connection string for the Excel file:
= Excel.Workbook(File.Contents(Filepath & "/R in PBI/Excel Source Data.xlsx"))
This worked great; however, this technique became challenging when trying to incorporate the parameter into the connection string for an R Script.
When I use an R Script as a data source, I like using the source() function, as I outlined in this blog post last year.
= R.Execute("source(""C:/Users/josyeat/Desktop/R in PBI/Models/1 K-Means Clustering.R"")"
My goal was to replace the part of the connection string to the left of the /R in PBI, exactly as I had done with the Excel workbook.
The first thing I tried was changing the connection string in the same way as I had above:
= R.Execute("source(Filepath & "R in PBI/Models/1 K-Means Clustering.R"")"
This didn’t work as I had expected it to. The issue was with the R.Execute() Power Query function. It executes everything in the quotations as an R script. My Filepath parameter was trying to be executing in R, which wouldn’t work!
After a few attempts, I was able to fix the issues by adding more quotations to the string around the parameter. I had to wrap the first section before the Filepath parameter in quadruple quotes:
= R.Execute("source("&""""& Filepath & "R in PBI/Models/1 K-Means Clustering.R"")"
This ensured that the parameter was being included into the R Script string as intended. The R script is now dependent on the same file path parameter as the Excel workbook!
This makes is much easier for the end user, as they just have to input the file path once when they open the .pbit file, and it flows through to all of the source files, regardless of what type of data source they are!