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.


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 😉

Writing data to GitHub using Power Query only

You shouldn’t do it …

Generally it’s a very bad idea to execute commands in Power Query that write out data to a source, as these commands might be executed multiple times (https://blog.crossjoin.co.uk/2018/10/07/power-query-write-data/)

… unless … maybe ?

However, as with most good rules, there are exceptions. I leave it to you to decide whether my use case here is a valid candidate for it. It doesn’t execute the code twice, because I execute the query only from the query editor and none of the other queries is referencing its results. But please see for yourself – Writing data to GitHub using just Power Query:

The video

Read more

Import text from pdf files in Power BI

While Power BI will soon provide functions to import tables from pdf-files, there might be occasions when you actually need to import text from pdf files (in unstructured form). With a little help from R in Power BI you can do exactly that. (And don’t worry: No need to learn R here: The necessary R-code is already included in my function below. All you need is to have R installed your machine). Please also note that at the time of writing the refresh of these queries in the service is only supported with the personal gateway and not with the enterprise version.


You can use the function below just like a normal M-function, just pass the (URL- or file-) path to it. All you have to take care of is that a instance of R is running on your machine. If this is new to you, check out Ruth Pozuelo’s video showing all the necessary steps: How to install R for Power BI

There is one package required: pdftools. The video above also shows how to install it.


Import text from PDF files:

You can try calling this function for a pdf-file from the internet like the M formula language specification like this:


If you want to import local files from your computer, just paste the full file-path instead of the URL. You don’t have to care about the direction of the slashes, both versions (forward and backwards) are accepted.

How to use

The script will return a table with one row for each page in the pdf-file by default. But it has an optional 2nd parameter that will return one row per pdf-text-line instead, if you put 1 into it. A page index and a row index will help navigating the result.

The 3rd parameter is an optional owner password for the pdf and the 4th the optional user password. If you’re using them, you have to enter null for the previous optional parameters. The following example shows how to use a user password while leaving the others “empty”:

ImportPdfText("MyPdfPath",null, null, "MyPassword")

Also check out Ruth Pozuelos video where she shows how to use this: https://www.youtube.com/watch?v=z15dF-jDXIo

Enjoy & stay queryious 🙂

How to create and use an R-function-library in Power BI

Edit 10-10-2017: There is also a (simpler) way to run a custom function library described here: http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/ . If you go that route, the only point of interest in the article might be how to create your function library automatically.

Once you’ve discovered the huge potential R gives you to expand your analytical toolbox in Power BI (check some tips & tricks in my previous blogpost if you haven’t already), you might wish to have all your awesome functions conveniently at hand when designing new solutions. And thanks to M, there’s actually nothing easier than that: R-function-library in a record (which works just the same for M-functions 🙂 )

Put your functions into a record (fnr) with the function name as the field name and the function itself as the value: One query to hold them all (and not cluttering your editor pane) and ready to use as if they were native functions:



will export content of my query “Actuals” to csv-file on my desktop.

  1. fnr is the name of the record. You can give it your own name of course, I prefer to keep this as short as possible.
  2. followed in square bracket is the name of the function (record field name)
  3. in ordinary brackets you have the function arguments just like in standard M (record value)

Create record

Read more

Tips and Tricks for R scripts in the query editor in Power BI

Especially if you are new to R, there are some things one needs to know to successfully run R-scripts in the query editor of Power BI. I will share them here along with some tricks that made my R-life in Power BI easier:

How to get started – useful links:


You can feed multiple tables into the R-script

If you click the icon “R script”, the table from the previous step will automatically be passed as the “dataset” to the R-script. So if you don’t fill in any R-code, this will happen:


But if you need the content from other tables as well, you just add them into the square brackets like this:


“Documentation” looks like this:


You can use parameters in the R-script

Apart from tables, you can also use text strings as parameters in the script. They need to be inserted into the code with preceeding “& and trailing &”:

RExportCsv= R.Execute(“write.csv(dataset,” “&CsvExportPath&” “)”,[dataset=Actuals])

Beware that they must be text. So if you want to pass a number, wrap it into Text.From(…).

You cannot use anything else apart from tables and parameters in the R-script

Well, at least I haven’t managed it Smile

R-life gets easy-peasy if you use M-functions for your R-script

Read more

Machine Learning with M in PowerBI and Excel

Very often I have thought about trying M instead of R for machine learning problems in PowerBI. Not only because I’m such a big fan of M, but also because we don’t have the R-integration in Excel (yet?).

Leila Etaati’s brilliant series of how to use R in PowerBI for KNN-prediction (nearest neighbourhood) finally kicked this off. In order to trigger some thoughts I have structured the code in a way that resembles the R-structure. So the core M-code looks like this:

KNN in M


Where this sits in a function that you feed with the following parameters:

Function Parameters


In there, 2 functions are called, like in the R-code. While the functions already exists in R and you just have to load the necessary packages, in M we don’t have these functions (yet), so I had to build them:

Normalizing the table:

Evaluating the nearest neighbour-label:

I also added some comfort-features: The k-value will be calculated automatically and you can enter a %-value for the split between training & test-data.


M has all it needs to calculate the results, but the performance can be a pain. To my understanding so far, this is mainly due to the fact that it will call the sources multiple times. Unlike in SQL-server for example, the execution plan is hidden and we also don’t have stored procedures which enable us to de-activate the re-evaluation of the execution plans with every data refresh.

While I see the point in not re-inventing the wheel, there is an aspect of how many languages we are expecting the PowerBI-users to learn. Just a thought.

File to download:
zipped pbix: KNNR2-1.zip

Enjoy & stay queryious 🙂