Fixing ADO.NET error trying to run Python Script in Power BI

While trying to use a Python script in Power BI Desktop, I ran into a strange error that made me lose a whole day trying to solve it. I wanted to share with you the solution I found, thus avoiding wasting your time as well.

How I came across this error

I was recently trying to perform the scoring of a machine learning model trained via Azure AutoML in Power BI Desktop using a Python script. At the end of the training of the model, Azure AutoML exposes in the folder of the outputs a YML file (conda_env_v_1_0_0.yml) containing all the dependencies in order to create a conda environment usable for the aforesaid scoring. In this file I simply renamed the environment name from “project_environment” to “azureml_scoring_env”. Here is its content:

I already have Miniconda installed on my machine, so I also have Anaconda Prompt available among my applications:

In the Anaconda Prompt you can run a conda command to create a new environment from a conda environment specification file. The command is as follows:

conda env create -f "C:\<your-absolute-path-to-the-yml-file>\conda_env_v_1_0_0.yml"

After running the command in the Anaconda Prompt, the new environment is created. To verify that the creation was done correctly, you can use this other command:

conda env list

In my case, what I get is this:

At this point I launch Power BI Desktop and, in the Options, I go to reference the environment just created in the “Python scripting” section:

Awesome! I’m ready to run a Python script for model scoring that makes use of the NumPy library in Power BI Desktop, already successfully tested in Visual Studio Code referencing the newly created environment. Now for simplicity I will use an example script, which simply lists the methods provided by the NumPy library in a Pandas dataframe:

import pandas as pd
import numpy

df = pd.DataFrame(dir(numpy))

So in Power BI Desktop I click on “Get Data”, start typing the string “script” in the search bar and select the “Python script” connector:

I paste the above Python script into the script editor that appears in the dialog, and after clicking OK, I get this error:

Wow, this is somewhat weird! I wrote a whole book on how to integrate Power BI with Python and R, and this error had never occurred to me. The first few lines of the message are really unclear. Going through the error text message, this relevant part of the message is evident:

“Unable to import required dependencies: numpy”. This is even more crazy, since the NumPy library is definitely present in the new environment. This can be verified by first activating the new environment with the following command:

conda activate azureml_scoring_env

and then through the following command that lists the packages installed in the environment:

conda list

This is what I get:

So what’s wrong? After many attempts, a question came to my mind: “Want to see that, although in the Options I correctly indicate the reference to the correct environment, Power BI Desktop fails to reference it correctly?”. Here’s what I tried to do.

What causes the problem

I needed to find a way to “force” the execution of the Python script in the newly created environment. I thought that a possible solution of creating eventual system environment variables was not viable, because the analyst must be free to be able to reference a different environment as needed in Power BI. So what I tried to do is:

Launching the Power BI Desktop executable from the Anaconda Prompt after activating the environment in which you want to run the Python script.

The command to use to launch Power BI Desktop is as follows:

“C:\Program Files\Microsoft Power BI Desktop\bin\PBIDesktop.exe”

At that point Power BI Desktop opens and, repeating the steps from before to run the sample Python code, what you get is this:

Now everything works great!

Unlike some of the other Python distributions, Anaconda requires the environment to be activated, which happens when you open the command prompt using the special Anaconda executable. Power BI Desktop will directly invoke python.exe which, in the case of the anaconda distribution, does not have an initialized environment out of the box. Hence the error.

This problem is also highlighted in Microsoft Docs and often goes unnoticed. But the fact that sometimes the script works without returning an error, despite not activating the environment, is misleading.

Okay, we have the workaround! But do we need to open the Anaconda Prompt, activate the necessary environment, and then run the Power BI Desktop executable from there every time we need to develop in Python? There is a convenient and simple solution that speeds up the operations.

A practical solution to the problem

The goal of this solution is to be able to run with a simple double click an instance of Power BI Desktop that can “see” the correct Conda environment. It is simply a matter of using a convenient batch script:

In the above script make sure you use the correct path to your Anaconda or Miniconda installation (CONDAPATH variable) and specify the name of the environment to be used (ENVNAME variable).

After saving your run_power_bi_in_conda_env.bat file, create a shortcut to it on your Desktop right-clicking on it, clicking on “Sent to” and then “Desktop (create shortcut)”. After that, rename the shortcut on your Desktop as “Power BI - azureml_scoring_env”, right-click on it, select Properties, go to the Shortcut tab and click on “Change Icon…”:

A message saying that the .bat file doesn’t contain any icon will pop up. Just click OK, then click Browse and select the Power BI Desktop executable (PBIDesktop.exe) you can find in the “C:\Program Files\Microsoft Power BI Desktop\bin\” folder. Then choose the Power BI icon:

At this point you will have an icon ready to be clicked, useful to run the Power BI Desktop instance that references the azureml_scoring_env conda environment:

Obviously you can create as many icons as there are different conda environments that you will use in the development of your reports by creating specific batch files.

Conclusions

When Power BI Desktop needs to run a Python script, it directly invokes the python.exe executable found in the environment folder referenced in Options. If the environment you want to use is a conda environment (also used by the Azure Machine Learning backend), Power BI cannot activate the environment before executing the script. This is why to date it is necessary to do the activation “by hand”.

The product team has an item in its backlog to support and detect different python environments, but it is not yet in a planned state. Please, vote this idea in order to prioritize this item:

Thank you!