Skip to main content

Time to read: 5 minutes

The July 2020 release of Power BI Desktop includes a new preview feature that allows you to «external tools”To more easily connect to Power BI models. Before July, external tools could connect to an open tabular model. With the new External Tools tab on the ribbon, as well as Power BI passing the connection information as an argument to the tool, it's a much more convenient experience.

There are three prominent community tools to get you started: DAX Studio, ALM Toolkit, and Tabular Editor. However, one of the great things about this new feature 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"? Although python is not a "tool" in the same sense as the "big 3" community tools focused on this month, I want to show how versatile the external tools feature is. I also want to encourage people to use their imaginations and also explore how Power BI is not really as closed as some people think, at least the data model ...

Some of these ideas are not unique to python, but there is enough variety in the Power BI and data science communities for people to find out if any of this could be useful within the context of their own environments, skills, and organizations.

Sample idea: code your own Power BI model documentation

What if there was a way to connect to a Power BI model from python and use a python container for it Tabular object model (TOM) .NET library? Maybe to import the model information to pandas DataFrames to help you create your own one-click model documentation? (There are already many ways to document a model; add your own python code to the list)

image-4317846

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 copy the table manually from data view, but it is quite limited. If you want more, you would have previously 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 just want to export. In situations where you may not always have access to DAX Studio or python is your tool of choice and you prefer to get data directly from a model in a pandas DataFrame and avoid the manual import and export steps in between, it is an option. Yet 6 million records? Child's play… However, for performance reasons and to avoid impacting your organization's resources in the Power BI service, avoid exporting this amount of data with the XMLA endpoint in Power BI Premium: Follow this scenario using Power BI Desktop in your local workstation.

image-2-7237873

By the way, the other day I made fun of a tweet about export capabilities from Desktop and was surprised by the variety of responses. Many of the responses did not focus directly on the explicit question about Desk (Focused on the limitations of the service and not the desktop, promoted external tools, etc.), but thanks to all who responded.

image-1-4123289

Sample idea: Visualize Power BI data with interactive python libraries

One of the main limitations of using python visuals in a Power BI report is that the visual must display a static image. That's good enough for some scenarios, but I've answered a lot of questions about the python + Power BI extension (and even hosted a BlueGranite webinar on the topic included below). In general, it is usually ideal for individual or team scenarios. Static image, performance, and the inability to control the libraries installed by Microsoft once deployed to the Power BI service often limit their use to scale.

Here's the original Power BI + python webinar showing current capabilities for using python visuals in Power BI reports (+ subscribe to the BlueGranite YouTube channel by hovering over the logo in the top left and pressing 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 display libraries like Plotly or Altair in your IDE of choice. You're not limited to exporting your visual as a still image and embedding it in a Power BI report (although you obviously still can and should when the circumstances are right).

For example, here is a full code solution that displays Power BI data visualized with Altair (note the hover tooltip that you wouldn't get in a Power BI report). You can avoid having to manually export your Power BI data first before entering it into the DataFrame.

image-3-8627750

The same advantage is valid with Plotly or any other visualization library. Perhaps you display Power BI data in a Dash app and code your data integration instead of relying on a manual export.

image-4-5844646

Sample idea: combine data from Power BI model with another data source in python

Although Power BI has Power Query to combine data from different sources, what if Power BI * is * one of the data sources? For example, there are scenarios where Power Query won't take care of everything. You may want to merge Power BI data with another Python DataFrame 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 couldn't build that geojson, but python could. NOTE: I don't have a screenshot for this scenario yet because it's still just an idea.

Coming soon in Part 2: Create an external python tool for the Power BI ribbon

Throughout 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 explain how to create an external tool pbitool.json to pass arguments to python and run a sample python script. Future posts after Part 2 will focus on more of the advanced scenarios described above, such as accessing TOM and executing DAX queries from a python script or Jupyter notebook.

 

 

R Marketing Digital