Python as an “External Tool” for Power BI Desktop: Part 1

Python as an “External Tool” for Power BI Desktop: Part 1
Reading Time: 5 minutes

The July 2020 version of Power BI Desktop includes a new preview feature that allows “external tools” to more easily connect to Power BI models. Prior to July, external tools could connect to an open tabular model. With the new External Tools tab in the ribbon as well as Power BI passing connection info as an argument to the tool, it’s a much more convenient experience.

There are three featured community tools to start out: DAX Studio, ALM Toolkit, and Tabular Editor. One of the great aspects of this new feature though is that you can define your own external tools in JSON and pass the currently open Power BI model connection to your tool with a simple click.

Why Python?

Why use Python as an external “tool”? Even though Python isn’t a “tool” in the same sense as the “Big 3” community tools focused this month, I want to show how versatile the External Tools feature is. I also want to encourage people to use imagination and also explore how Power BI isn’t really as closed as some people think–at least the data model…

Some of these ideas are not exclusive to Python, but there’s enough variety in the Power BI and data science communities for people to possibly figure out if some of this might be useful within the context of their own environments, skills, and organizations.

Sample Idea: Code your own Power BI model documentation

What if there were a way to connect to a Power BI model from Python and use a Python wrapper for the Tabular Object Model (TOM) .NET library? Maybe to import model info into pandas DataFrames to help build your own single-click model documentation? (There already are many ways to document a model–add code your own in Python to the list)

Sample Idea: Export as much data as your local memory allows

Power BI Desktop allows you to export 30,000 records to CSV from a visual. You can get more if you manually Copy Table from the Data view, but it’s fairly limited. If you want more, you’d previously have had to use DAX Studio or Power BI Report Builder. Add Python to that list.

Note that exporting from DAX Studio is faster if you simply want an export. In situations where you may not always have access to DAX Studio or Python is your tool of choice and you’d rather get data directly from a model into a pandas DataFrame and avoid the middle manual export and import steps, it’s an option. 6 million records though? Child’s play… For performance reasons and to not impact your organizational resources in Power BI service though, avoid exporting this much data with the XMLA endpoint in Power BI Premium–stick to this scenario using Power BI Desktop on your local workstation.

By the way, I teased a tweet the other day on the export capabilities from Desktop and was surprised by the variety of responses. Many of the replies didn’t directly focus on the explicit question asked about Desktop (focused on limitations of the service and not Desktop, promoted external tools, etc.), but thanks to everyone who responded.

Sample Idea: Visualize Power BI data with interactive Python libraries

One of the primary limitations of using Python visuals on a Power BI report is that the visual must display a static image. That’s good enough for some scenarios, but I’ve answered many questions about extending Python + Power BI (and even hosted a BlueGranite webinar on the topic embedded below). Overall, it’s often great for individual or team scenarios. The static image, performance, as well as inability to control the libraries installed by Microsoft once deployed to the Power BI service often limits its use at scale.

Here’s the original Power BI + Python webinar showing the current capabilities to use Python visuals in Power BI reports (+ please Subscribe to BlueGranite’s YouTube channel by hovering over the logo in the top left and hitting the red Subscribe button):

By connecting to the Power BI model and running a DAX query to load a pandas DataFrame, you can also use interactive Python visualization libraries such as Plotly or Altair in your IDE of choice. You’re not limited to exporting your visual as a static image and embedding it into a Power BI report (although you obviously still can and should when the circumstance is right).

For example, here’s an all-code solution showing Power BI data visualized with Altair (note the tooltip on hover you wouldn’t get in a Power BI report). You can avoid having to manually export the data from Power BI first before getting it into the DataFrame.

The same advantage holds true with Plotly or any other visualization library. Perhaps display Power BI data in a Dash app and code the data integration rather than have to rely on a manual export.

Sample Idea: Combine Power BI model data with another data source in Python

Although Power BI has Power Query to mashup data from different sources, what if Power BI *is* one of the data sources? For example, there are scenarios where Power Query won’t handle everything. Perhaps you want to merge Power BI data with another DataFrame in Python containing geographic data and export the results as a geojson file (for use in the new Power BI Azure Maps visual, of course!). Power BI would not be able to build that geojson, but Python could. NOTE: I don’t have a screenshot for this scenario yet because it’s still only an idea.

Coming in Part 2: Create a Python External Tool for the Power BI ribbon

Over a series of upcoming posts, I’ll show you how to get started with scenarios like this using Python with Power BI models. In Part 2, I walk through how to create an external tool pbitool.json file to pass arguments to Python and run a sample Python script. Future posts after Part 2 will focus on more of the advanced scenarios outlined above such as accessing TOM and running DAX queries from a Python script or Jupyter notebook.

If you haven’t already, subscribe to the DataVeld blog to be notified when new posts in this series are available.

Paid Links

As an Amazon Associate I earn from qualifying purchases.


Leave a Reply

%d bloggers like this: