Skip to main content
Power BI

Jupyter as an external tool for Power BI Desktop (Python Part 4)

Time to read: 4 minutes

Many people use Python with notebooks, so let's take a look at one possible way to enable a Jupyter external tool for Power BI Desktop. The next tiered approach starts with simply opening Jupyter. Then go ahead to create and open a notebook that includes Power BI server and database arguments. Finally, you work your way to downloading a notebook definition contained in a GitHub essence and plugging into the Power BI tabular model to start making this approach more useful.

This post continues a series of posts related to Python and Power BI. The first three parts of this blog series introduced some possible uses of Python connected to a Power BI model, how to configure a basic external Python tool, and how to use it with a virtual environment and connect to the tabular object model.

Open Jupyter: essential but not useful

I use the Anaconda distribution in these examples, and let's consider how to just start and open a notebook (vanilla Jupyter and nothing with JupyterLab). We only need a pbitool.json file at this stage because the goal is to just start Jupyter, not even a binder file. It is not a useful example, but it provides a foundation on which we will build.

As discussed in Part 2, the pbitool.json files define the external tools for Power BI Desktop. If you look at the Jupyter shortcut, it provides the inspiration on how to define the Power BI external tool.

image-9396482

The JSON in this essence should be saved as a [tool] .pbitool.json and placed in C: Program Files (x86) Common Files Microsoft Shared Power BI Desktop External Tools.

Note that the path is for Python.exe and the arguments load the default Python environment and run the jupyter-notebook-script.py script to start Jupyter.

image-1-5659520

However, simply opening Jupyter is of no value. At this stage, it is nothing more than a replacement for a shortcut. The real value is integrating Power BI Desktop more closely with the laptop.

Create and open a notebook (coded)

Going one step further, let's create an empty notebook and write the currently open Power BI Desktop connection information into the notebook cells. In this case, we need a Python script along with a new [tool] .pbitool.json file.

The [tool] .pbitool.json file has some differences from the previous version. In particular, it runs a python script that I have named powerbinotebook.py and placed in my C: Temp directory. You will also see the server and database Arguments that Power BI Desktop uses to pass the current local connection (server: database GUID and port).

C: /Temp/powerbinotebook.py ”% server%” ”% database%”

The rest of the arguments start the default Python environment and run Jupyter, but it is important that the powerbinotebook.py The script runs before Jupyter starts. Why is that?

The Python script defines a nearly empty notebook for Jupyter and then launches it in Jupyter. Pay close attention to notebook_core, which contains the JSON definition for the binder and writes sys.argv [1] and sys.argv [2] to the binder for the Power BI server and the database respectively. Each cell in the cells The matrix will appear as a separate cell in the sample notebook. As required by the pbitool.json definition above, the Python script is located at C: Temp powerbinotebook.py, and obviously you could name and save yours in a location of your choice.

Maybe you are interested >>>  Power BI DAX math functions
image-2-8664612

The following video shows the basic execution of the script, where the Python script creates the notebook, starts Jupyter, and Jupyter starts the notebook.

Create and open a notebook defined in a GitHub Gist

Going one step further, let's change the notebook definition to a GitHub essence instead of coding it in the Python script. Essence contains JSON and also placeholders for <and <, which the Python script will replace with the actual sys.argv [1] and sys.argv [2] values.

Essence example (not embedded in post): https://gist.github.com/deldersveld/fdada06b82621233d6e40fac13cceb55

I used the same [tool] .pbitool.json, since it only changes the content of the powerbinotebook.py script. Note that the script is much shorter now that it no longer contains the definition of the notebook. This uses the requests library to get the JSON notebook template from the url defined in gist_url. Also notice how replace swaps current Power BI database and server settings.

Expanding the definition of the notebook

Finally, let's build a slightly more complex notebook with an update powerbinotebook.py screenplay. As before, no modifications are required for [tool] .pbitool.json. Only the Python script is updated.

Here is the updated notebook Gist for reference (not embedded in post):
https://gist.github.com/deldersveld/70e05dbc97086181641592f03d314800

As with the last example, the Python script downloads the new notebook definition contained in a different GitHub essence. Also, as before, replace the placeholders <

What's new here is that this script downloads the ssas_api.py module mentioned in the Part 3 post, which is one of many possible ways to connect Python to AMO / TOM and other .NET libraries. The updated sample notebook loads the module and demonstrates the connectivity of the base model.

PLEASE NOTE that the script does not intentionally overwrite a notebook once it is created for a specific port number. To get a new blank template if you are testing this, it simply requires closing and reopening Power BI Desktop. The connection to TOM in this example also requires a Power BI Desktop connection to at least one existing table.

Here's a look at the sample notebook in action. It illustrates two things: a basic connection to TOM and evaluating a DAX query with the model open in Power BI Desktop.

Unlocking more

With a live connection to the Python Power BI Desktop model, you can unlock many possibilities. As described in Part 1, there are many examples ranging from data visualization to model update. I'm curious for your thoughts on how you would like to use Python in Power BI.

Let me know in the comments and don't forget to subscribe to the DataVeld blog.

Payment links

As an Amazon Associate, I earn on qualifying purchases.

error: Attention: Protected content.