Python as an “External Tool” in Power BI Desktop: Part 2 – Create a PBITool.json File

In Part 1, I introduced a few possible scenarios to use Python with Power BI outside of the built-in Python visual and Python script features. This post focuses on setting up a basic external tool file that passes the Power BI data model connection for use in Python scripts. It’s going to be a very basic scenario, but it lays the foundation for hopefully better things to come as described in Part 1.

Define a Basic PBITool JSON file

To see a new tool in the Power BI Desktop ribbon, you need to define a JSON file and place it in a specific folder on your workstation. The featured external tools Tabular Editor, DAX Studio, and ALM Toolkit have installers that take care of this step. Since you do not have a dedicated installer to place this file in the required directory, you need to manually define your own.

As long as the “enhanced metadata format” for the data model is enabled, and the JSON in your file is accurate, you should see your new tool in your ribbon after you re-open Power BI Desktop.

Let’s define an External Tool called “Power BI Desktop Connection” that displays the Python logo in the ribbon and shows the current Power BI connection information when you click it.

To get started, copy and paste the following JSON into a text editor (gist).

{
  "version": "1.0",
  "name": "[Tool Name]",
  "description": "[Tool Description]",
  "path": "C:\\[PATH TO PYTHON EXECUTABLE]\\python.exe",
  "arguments": "C:/[PATH TO PYTHON SCRIPT].py \"%server%\" \"%database%\"",
  "iconData": "data:image/png;base64,[YOUR BASE64 IMAGE CONTENT]"
}

Here’s an example I filled out for my basic sample tool to display the current Power BI Desktop connection. I’m using Anaconda, which is why you see python.exe from Anaconda’s default location in my path.

 {
  "version": "1.0",
  "name": "Power BI Desktop Connection",
  "description": "Use Python",
  "path": "C:\\ProgramData\\Anaconda3\\python.exe",
  "arguments": "C:/Users/DavidEldersveld/Documents/Python/PowerBIConnectionString.py \"%server%\" \"%database%\"",
  "iconData": ""
}

Breaking down the contents:
– version: don’t change this
– name: display name in the Power BI Desktop ribbon
– description: description of the external tool (you’d think that this would surface as alt text when you hover, but only the name displays on hover)
– path: path to your python.exe file
– arguments: command line arguments passed to your executable
– iconData: base64 image used for the ribbon icon

If you look at the [tool].pbitool.json file for another tool like Tabular Editor, you’ll see the server and database passed as the only two arguments. For use with Python, those can remain, but your first argument will need to be a .py Python script in this case.

To adapt this file to your own, determine the appropriate values and replace as required.

 {
  "version": "1.0",
  "name": "[Tool Name]",
  "description": "[Tool Description]",
  "path": "C:\\[PATH TO PYTHON EXECUTABLE]\\python.exe",
  "arguments": "C:/[PATH TO PYTHON SCRIPT].py \"%server%\" \"%database%\"",
  "iconData": "data:image/png;base64,[YOUR BASE64 IMAGE CONTENT]"
}

The two most important will be the correct path to your python.exe and the first argument value with the correct path to your Python script.

I know that there’s a way to use code to convert images to base64, but I always end up using a website like this one for convenience: https://www.base64-image.de/

Add your [tool].pbitool.json file to the correct folder

Add your new file to your C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools folder.

Create your Python script

The [tool].pbitool.json file used a .py file as its first argument. Where did that come from? It comes from you! Drop whatever code you want to execute into that file and save it in the location that you provided in the [tool].pbitool.json file.

For my Power BI Desktop Connection example, my sample script (gist) is below, which I saved as PowerBIConnectionString.py in the path described in the earlier [tool].pbitool.json file. The script simply prints the server and database arguments passed to it, then concatenates and prints a connection string that could be used to connect to that particular data model.

import sys

print('Power BI Desktop Connection')
print(str(sys.argv[1]))
print(str(sys.argv[2]))

print('')
conn = "Provider=MSOLAP;Data Source=" + str(sys.argv[1]) + ";Initial Catalog='';"
print(conn)

input() 

sys.argv[1] is the argument corresponding to server and sys.argv[2] is the database GUID. Python starts at 0, which is the argument value associated with the current script.

If you eventually extend External Tools with your own Python scripts and pass the Power BI Desktop connection, you’ll rely on sys.argv[1].

The database GUID will come into play in future scripts but is not needed for this initial scenario. Just know that locally, the connection string will only need server:port.

If both the .py script and [tool].pbitool.json file are in the correct location, and assuming the script works, you can open Power BI Desktop. Check the External Tools ribbon to execute the script.

Coming in Part 3: .NET + Python – Connecting to the Tabular Object Model (TOM)

While this basic script works as-is, there’s an issue with this setup that I’ll cover in the next post. Python.exe runs standalone outside of an environment, so any packages loaded into the base or an alternative virtual environment are not available for the script.

To connect to the tabular object model from Python, we’ll need a little help from .NET and some packages in my conda environment. Part 3 contains the tweaks to the [tool].pbitool.json file that allow me to use a targeted Python virtual environment.