Skip to main content

Time to read: 4 minutes

In Part 1, I presented some possible scenarios for using python with Power BI outside of the built-in visual python and python script functions. 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 will 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 on the Power BI Desktop ribbon, you must define a JSON file and place it in a specific folder on your workstation. the Featured The external tools Tabular Editor, DAX Studio, and ALM Toolkit have installers that handle this step. Since you don't have a dedicated installer to put this file in the required directory, you need to manually define yours.

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 on the ribbon after you reopen Power BI Desktop.

Let's define an external tool called "Power BI Desktop Connection" that displays the python logo on the ribbon and displays the current Power BI connection information when you click on it.

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

  "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 is an example that I completed for my basic sample tool to show the current Power BI Desktop connection. I'm using Anaconda, that's why you see python.exe from the default Anaconda 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 / "%server%" "%database%"", "iconData": ""}

Breaking down the content:
version: don't change this
Name: display name on Power BI Desktop ribbon
description: external tool description (you would think this would appear as alt text when scrolling, but only the Name displayed when hovering)
way: way to you python.exe proceedings
arguments: command line arguments passed to your executable
iconData: base64 image used for ribbon icon

If you look at the [tool] .pbitool.json for another tool like Tabular Editor, you will 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 python .py script in this case.

To adapt this file to yours, determine the appropriate values and replace them as necessary.

 {"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 there is a way to use code to convert images to base64, but I always end up using a website like this for convenience:

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 binder.

Create your python script

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

For my Power BI Desktop Connection example, my sample script (essence) is below, which I saved as in the path described above [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 the server and sys.argv [2] is the GUID of the database. python starts at 0, which is the value of the argument associated with the current script.

If you finally extend the external tools with your own python scripts and pass the Power BI Desktop connection, you will trust sys.argv [1].

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

If both the .py script and [tool] The .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 run the script.

Coming soon in Part 3: .NET + python - Connecting to the Tabular Object Model (TOM)

While this basic script works as is, there is an issue with this setup that I will cover in the next post. python.exe runs independently outside of an environment, so packages loaded into the database or in an alternate virtual environment are not available to the script.

To connect to python's tabular object model, we'll need a little help from .NET and some packages in my conda environment. Part 3 contains the settings to the [tool] .pbitool.json file that allows me to use a specific python virtual environment.

Payment links

As an Amazon Associate, I earn on qualifying purchases.