Power BI R query incredibly slow, when it should not be, any tips?

[Saarek]

I've struggled for the last few weeks to refresh a report within PowerBI that uses R as the source.

This morning I went as far as to export all of the date frames as rds files to a folder on my desktop and then create a simple script to read in the RDS files. The code is here:

setwd("C:/Users/david.1.robinson/Desktop/Export")

Casualty_Claims <- readRDS(file = "Casualty_Claims.rds")
Combined <- readRDS(file = "Combined.rds")
Finance_Transactions <- readRDS(file = "Finance_Transactions.rds")
Predict_Final <- readRDS(file = "Predict_Final.rds")
Property_Claims <- readRDS(file = "Property_Claims.rds")
Prophecy <- readRDS(file = "Prophecy.rds")
Tiering <- readRDS(file = "Tiering.rds")

The total file size of all of these files is just 129 MB. If I run this code in R Studio it takes just 6 seconds to bring in the data.

With Power BI it is taking 17 minutes. Surely it should be impossible for Power BI to be so inefficient? Is there anyway of speeding Power BI up with regards to R?

[v-alq-msft]

Hi, @Saarek 

I am not familiar with R. Here are some considerations and limitations when using R in Power BI Desktop you may refer.

To run the script in Power BI Desktop, make sure the script runs successfully in a new and unmodified workspace. This prerequisite means that all packages and dependencies must be explicitly loaded and run. You can use source() to run dependent scripts.

When you prepare and run an R script in Power BI Desktop, there are a few limitations:

• Because only data frames are imported, remember to represent the data you want to import to Power BI in a data frame.
• Columns typed as Complex and Vector aren't imported, and they're replaced with error values in the created table.
• Values that are N/A are translated to NULL values in Power BI Desktop.
• If an R script runs longer than 30 minutes, it times out.
• Interactive calls in the R script, such as waiting for user input, halts the script’s execution.
• When setting the working directory within the R script, you must define a full path to the working directory, rather than a relative path.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Saarek]

Hi Allan,

Appolgies for my delayed response. I've spoken with colleagues who have run into the same issue. For basic R Code Power BI does fine, but when trying to generate the dataset through R in Power BI it just runs at a snails pace.

I now run my R Code in Rstuido, output to csv file.

Originally I'd hoped that others could just hit refresh in Power BI and job done, but it's not to be.