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 stepwise approach starts with simply opening Jupyter. Then go ahead to create and open a notebook that includes Power BI server and database argumentos. Finalmente, se abre camino hacia la descarga de una definición de cuaderno contenida en una esencia de GitHub y se conecta al modelo tabular de Power BI para comenzar a hacer que este enfoque be más útil.
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 set up a basic Python external 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 distribución en estos ejemplos, y consideremos cómo simplemente iniciar y abrir un cuaderno (vainilla Jupyter y nada con JupyterLab). Solo necesitamos un archivo pbitool.json en esta etapa porque el target es simplemente iniciar Jupyter, ni siquiera un archivo de cuaderno. No es un ejemplo útil, pero proporciona una base sobre la que construiremos.
As discussed in Part 2, the pbitool.json files define the external tools for Power BI Desktop. Looking at the Jupyter shortcut provides the inspiration on how to define the Power BI external tool.
<>
The JSON in this gist 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.
However, there is no value in simply opening Jupyter. At this stage, it is nothing more than a shortcut replacement. 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 type the currently open Power BI Desktop connection information into the cells of the notebook. 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 is executed 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 notebook and writes sys.argv[1] and sys.argv[2] to the notebook for the Power BI server and database respectively. Every 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\powerbi\notebook.py, and obviously you could name and save yours in a location of your choice.
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 a step further, let's change the notebook definition to a GitHub gist instead of hardcoding it in the Python script. The gist 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
Yo usé lo mismo [tool].pbitool.json, ya que solo cambia el contents del script powerbinotebook.py. Tenga en cuenta que el script es mucho más corto ahora que ya no contiene la definición del cuaderno. Esto usa el requests biblioteca para obtener la plantilla de cuaderno JSON de la Url definida en 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 to [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 gist. Also, as before, replace the <> and <> placeholders with the actual values passed to sys.argv from Power BI Desktop.
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.
NOTE that the script does not intentionally overwrite a notebook once it is created for a specific port number. Getting a new blank template if you're trying this out simply requires closing and reopening Power BI Desktop. The connection to TOM in this example also requires a Power BI Desktop connection with 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 open model 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 updating. I'm curious to hear your thoughts on how you would like to use Python in Power BI.
