Bug in Power BI R Scripts “package … was installed with different internals”

Today I spent many hours hunting an R-script error in Power BI and before Steph Locke came up with a solution for this, I came across a couple of posts and heard of other people, having the same problem. This blogpost is to make distribution of the solution a bit easier and to hopefully to help other folks with the same problem in the future.

The Problem

When running R-scripts in Power BI, I got all sorts of error-messages who all had one thing in common: They were complaining about one or more packages being installed by an R version with different internals.

They ran without any problem in RStudio or on other machines, just not on my own specific laptop.

The Solution

I have no idea what causes the problem, but Steph Locke showed me how she solved it before. She installed the problematic packages into R’s program-folder and pointed to this folder in a parameter, when using the function.

Step-by-step-instruction

1) Find your paths

Display your R-paths by using this function: .libPath()

The first path is the one which Power BI most likely will reference by default for the package information and the one which RStudio uses to install the packages to. The second path in the program folder belongs to a folder that also contains the R-program itself.

Now to solve the problem you have to install the packages that turn up in the error-messages into this second library folder.

2) Install package into the program folder

Therefore you have to open the RGui with admin-rights. If you don’t have a symbol for it on your desktop, you’ll find the file in the bin-folder:

install.packages("scales",lib="C:\\Program Files\\Microsoft\\R Open\\R-3.5.2\\library")

The fist function-parameter takes the name of the problematic package and in the second parameter you have to pass in the path to the library folder within R’s program folder. That’s the 2nd folder from the step above. Make sure to turn the slashes. The double-slashes might not be necessary for everyone, but for me it wouldn’t work otherwise.

3) Adjust R-script

In this last step, you have to add one or more lines of code on top of your existing code:

library("YourPackageName", lib = .libPaths()[-1] )

This formula will load the package, and the 2nd parameter will determine the path from which the file will be taken. Here, the first item from the paths from step 1 will be skipped, so the library in the program folder will be chosen instead.

Just install one package, run the script again, see if another package pops up – rinse – repeat – until you’re done ūüėČ

Please vote for the bugfix here: https://community.powerbi.com/t5/Issues/R-visual-error-quot-package-was-installed-by-an-R-version-with/idi-p/512759

& stay queryious ūüėČ

SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel

Edit: There is currently no interest at Microsoft to change the current behaviour: http://community.powerbi.com/t5/Issues/Bug-Query-folding-not-working-with-non-SQL-datasource/idi-p/225100#M11611 .

I found that this workaround works also pretty well (faster in most cases), as long as your filter-tables are not too long: http://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503

Are your SQL queries that filter with a non-SQL-table slow in PowerBI and PowerQuery in Excel? Then this might be of interest for you:

Nearly 2 years ago when I published my first blogpost about the bug that prevents query folding on SQL-sources when filtered by non-SQL-sources, PowerBI was still so new that I even didn’t mention it in my blogpost. I had enormous problems with some clients work with performance and spent 2-digit hours on figuring out the reason with some good help from the TechNet-Forum.

Today I was notified that “this bug was deemed as not high-pri enough to warrant a fix”. So it’s well alive and sucking: In Excel as well as in PowerBI. Considering how many people read my article, I¬†found it worth to mention again and provide an improved solution. It¬†is a function that’s easy to implement and takes the name of the tables and its key-columns as parameters:

Code to fix Query-Folding bug in PowerBi and PowerQuery

Code to fix Query Folding Bug in PowerBI and PowerQuery

And the code to copy: FilterSQLTable.txt

Hope this prevents you from wasting precious time.