Integrating Power BI and Python

Author: Ray Johnson Posted In: Data

For those who don’t know, Power BI has the ability to integrate with other platforms used to develop advanced analytic solutions, specifically, “R” and “Python”. In this post, I will present a simple example illustrating the integration of Power BI and Python with an additional goal of getting the reader thinking about other ways of leveraging Power BI and Python.

Background

The first question is why would you use Python with Power BI? Well, Power BI provides access to a large number of data visualization and data blending capabilities. Python’s data engineering features and frameworks extend Power BI capabilities and also provide capabilities that are not available in Power BI.

Configuration

To get started you need a Python distribution installed and install packages you wish to use. I am using Anaconda Python. Turn on the Python Support under Power BI preview features (File -> Options and Settings -> Options -> Preview features -> Python Support).

Activate Python Preview Support preview feature

Finally, point Power BI to the instance of Python you are using. At this time you can also set up an external IDE  to link to. The external IDE is definitely a personal preference and is sometimes the cause of intense debate (subject for a future post).

Python Scripting Options

Now, we are ready to use Python with Power BI. Python is used for two tasks in Power BI: to import and manipulate data, and to produce data visualizations.

Loading Data

Importing data into Power BI is fairly simple. In Power BI select Home -> Get Data -> Other -> Python script. The Python scripting window opens and you can enter your code. I will import the ever popular iris dataset formatted as a CSV file with headers.

Loading data using Python

After you click OK, close the query window and the data will be loaded. The key point here is that you have all of the power of Python at your disposal to perform data engineering tasks as well as Power BI’s query editor. Following is a Power BI report showing the loaded data.

Sample of loaded Iris data

Data Visualization

For the data visualization I am going to display a correlation plot and a box plot. The box plot will be based on the Sepal.Length. To create the visualization, place a Python Visual on the canvas.

The Python script editor will be displayed. Select the fields to include in the visual. Power BI will create the necessary code stub to make Power BI data available as a dataset inside of the visual. Import any additional libraries and write the required code to produce the visualization. The results of your Python code must produce a visualization or an exception will occur.

Here is the code for the correlation plot visual:

#############################################

# Import require modules
import seaborn as sns
import matplotlib.pyplot as plt

# Create correlation plot
sns.pairplot(dataset, hue = “Species”)
plt.show()

#############################################

Here is the code for the box plot visual:

#############################################
# Import required libraries
import seaborn as sns
import matplotlib.pyplot as plt

# Define plot aesthetics
sns.set_style(“darkgrid”)
sns.set_context(“notebook”, font_scale=1.2)

# Create box plot
sns.boxplot(data=dataset, x=”Species”, y = “Petal.Length”, hue=”Species” )
plt.show()
##############################################

Results

In the final result, a slicer has also been placed on the canvas. The slicer will interact with the Python visuals to provide filtering. Below is the completed report. Species is being used as a slicer and only versicolor and virginica have been selected. This is reflected in the Python visuals.

Power BI Python Visualizations

Summary

This is a simple example of what can be done when Python is integrated with Power BI. Controls from the Power BI marketplace could have been used to build the example; however, it is the increase in overall capabilities and flexibility provided via this integration, which allows the development more complex data engineering and advanced analytic solutions.