By now there’s plenty of good tutorials that explain how to create your own visualisations in Power BI by using R or Python like the Microsft one here.
However, I haven’t seen any articles on how to create dynamic visuals by using Python (or R).
So in this post, I’m going to show a very simple and fast way to create your power bi visuals by using python. (Same can be done using R)

Goals of this Post

• Create visuals by using python
• Avoid tedious hardcoding
• Create reusable scripts

Setting up the Environment

• Python: Download here.
• How to set up Python in PBI: here.
• Install the following python libraries: Pandas, Matplotlib, Seaborn, Numpy (The link above provides all the technical details to install these libraries)

Visuals available in Power BI

There are 3 different types of visuals in Power BI:

Out of the box:
PBI fully interactive visuals but cannot be customized (Microsoft property).
There are around 25 visuals available but you might be rapidly limited if you need to create more specific visuals.

Custom visuals:
Can be downloaded from the office store (some are free some are not).
You can also create your own Custom visuals but it will require a lot of hours to develop and good programming skills such as D3 or JQuery…
They are fully or partially interactive and cannot be customized (unless you’re the author or if they’re open source).

R and Python:
The visuals created by R or Python are usually not interactive as they render like an image or HTML if you use specific libraries (read my post here on how to create interactive R visuals in Power BI).

Main benefits of creating your own python visuals:
– Quick to create (require very little python knowledge)
– You can make very attractive visuals (Seaborn library)
– You can fully customize your visuals (will require more python skills)
The main downside of creating your own python visuals:
– You have to hardcode the column names
– We cannot reuse script (columns hardcoded)

So through this post, I’m going to show how we can overcome some of the downsides mentioned above.

Let’s create our first visual: Box plot

Box or Whisker plots are great to depict the distributions of one or more groups of numeric data. 

Boxplots provide a lot of insight such as data skewness, data symmetry, outliers or even side by side distribution comparison (Churner vs Non-Churner).

Anyway, I love to use Box plots, we can download an already made boxplot from the Office Store, however, it has some limitations: 

We cannot visualize two categorical variables with the already made box plot.

Let’s create a Box plot with two categorical variables

The below visual allows us to compare side by side the Monthly Charge distribution for the Churner and non-Churner customers as well as the customers with and without a phone service.

This chart provides good insight on who are the churners and it can be done in only one minute. So let’s do it!

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 
# dataset = pandas.DataFrame(Churn, MonthlyCharges, PhoneService)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 
# dataset = pandas.DataFrame(churn)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
import seaborn as sns
import matplotlib.pyplot as plt
plt.figure(figsize=(12, 6))
sns.boxplot(x = dataset['Churn'],y = dataset['MonthlyCharges'],hue=dataset['PhoneService'])
plt.show()

So with only a few lines of code, we can easily generate our visual.

However, if we were to create multiple Box plots or reuse it with other columns we would need to hardcode the column names again and again.
Now let’s drag another column in the values pane and see what happens.

It doesn’t run 🙁 

That is because the column names are hardcoded so every time we want to visualize another column we need to alter the script.

Now imagine we want to analyse 20 different columns, are we going to change the script 20 times? Well, I’m not and I hope you won’t!

Let’s fix it then!

Let’s create a dynamic Box plot

The code below allows us to dynamically generate a Box Plot with one or two variables and it also creates the title dynamically.

# The following code to create a dataframe and remove duplicated rows is always executed and acts as a preamble for your script: 
# dataset = pandas.DataFrame(churn)
# dataset = dataset.drop_duplicates()
# Paste or type your script code here:
# This script is ready to use in your Power BI
# It generates a box plot with 2 or 3 variables
# Dynamic title
# You need to drag X,Y,Hue in the right order
import seaborn as sns
import matplotlib.pyplot as plt
nb_cols=len(dataset.columns)
col_x=dataset.iloc[:,0]
col_y=dataset.iloc[:,1]
if nb_cols > 2:
    col_h=dataset.iloc[:,2]
    plt.figure(figsize=(12, 6))
    ax=sns.boxplot(x = col_x,y = col_y,hue=col_h)
    ax.set_title(dataset.columns[0] + ' by ' + dataset.columns[1] + ' and ' + dataset.columns[2] ,fontsize=20)
else:
    plt.figure(figsize=(12, 6))
    ax=sns.boxplot(x = col_x,y = col_y)
    ax.set_title(dataset.columns[0] + ' by ' + dataset.columns[1] ,fontsize=20)
plt.show()
# This script can be improved:
# Dynamically detecting column X and column Y
# Improve design, legend, labels, colours...

Let’s visualize it:

Now we can drag any columns we want and we can copy/paste the script in any of our Power BI without needing to hardcode anything.
Note that the above script can still be improved (design and error handling) but I just wanted to show the great capability of using python to create advanced charts while still keeping things simple.

In my view, even though there’s some limitations Python/R visuals are a great addition to the existing PBI data visualizations.
It literally takes only one minute to create a chart like this one and we can create much more complex and nicer visuals than this one!

Let’s Recap what are the benefits/downsides of using Python/R visuals:

• Create and customized your charts to fit specific needs
• Require very little python or R knowledge
• Can be easily reused
• we can make it dynamic no need of hardcoding
• It’s very fast to develop compare to other custom charts
• Need ton Install Python or R
• We can publish Power BI reports with Python visuals (most of python/R libraries are supported)
• However it’s not interactive (render as an image)
• Can be more interactive (will require extra work)