Python in Power BI: Enabling Python Scripts with Anaconda Environment

One of the nicer features of tools like Power BI and Tableau is that you can use a plethora of connection types to get both the data you need and visualizations that you need. Power BI allows users to leverage their Python knowledge in a manner that can be very easily shared with others once promoted to the server. If you are not familiar with Python, I highly recommend practicing in a Python IDE first before using it within Power BI (PBI).

Set Up

While the Power BI Documentation makes using a Python script as a source pretty easy, it's not so simple if you used an Anaconda environment.

Our first step is to change our PBI settings. Open PBI and simply go to File > Options & Settings > Options. Under the Global heading on the side, choose Python Scripting. You’ll want to navigate to your Anaconda environment, which should be C:\Users\<UserName>\Anaconda3. Click OK, and then go ahead and close PBI.

When using Python in PBI with an Anaconda environment, you have to launch PBI from said conda environment... which can be a little tricky the first time around.

First, open your command prompt, and enter “conda activate”.

At this point, ensure the following libraries along with any others in your script are both installed and updated using the “conda install” or “conda update” commands:

1. pandas
2. matplotlib
3. numpy

The online consensus is that you then need to open PBI from command prompt, but if you are operating in Windows 10, you need admin access to the WindowsApp folder, which isn’t feasible for everyone. Furthermore, it seems you may get an error stating 'The provider 'Microsoft.PowerBI.OleDb' is not registered’ if you don’t open PBI from the Start menu! So you can enter the path of the PBI executable if you really want, but if you get connection or load errors, simply open from the Start menu after you have activated you conda environment.

Load Your Data

Once PBI is open, navigate to the Get Data menu, and choose Python script.

Enter your Python script, but keep in mind that it must meet the following requirements:

1. The output must be a Panda’s dataframe, and it cannot be nested
2. The script cannot run longer than 30 minutes or require interactive calls
3. End the script with print(df)

On the next screen, check the box next to your dataframe’s name, and hit Load!

Your data is now available in Power BI!

When you are done, save and close your file as per usual. In the command prompt, simply type “exit” to close down the conda environment.