Last Updated on November 6, 2022 by Jay
This tutorial will walk through how to use Python visual inside PowerBI. Microsoft’s PowerBI comes with roughly 30 “core” data visualizations and overall has some of the finest aesthetics of any dashboarding tool on the market.
Why Use Python Visual Inside PowerBI
Sometimes we still want to use Python for visualization, such as:
- When we’ve already created the visualizations in Python and don’t want to recreate them in Power BI.
- Or when we don’t like any of the options in the core visualizations or available on the AppSource.
For those times, Microsoft included the “Py” visualization, which gives us a textbox to write any valid Python code we want in order to derive our final visualization. We can use any Python charting library including seaborn or plotly. The Py visualization is located alongside all other visualization types, in a pane on the right labeled Visualizations.
While they’ve made the process of using Python inside Power BI extremely straightforward and as painless as possible, be aware that it uses our local Python instance and libraries. So as soon as we reach for Python to create any visuals, we are giving up the ability to refresh this dashboard on Microsoft’s servers at [powerbi.com](https://app.powerbi.com). With that said, let’s get started!
Setup
Perhaps obviously, in order to use Python with PowerBI we need to have both Python and PowerBI installed. Note that we will also need the pandas and matplotlib libraries at a minimum. Any additional libraries we will need for the code will also need to be installed. We can install those libraries in the usual ways like pip.
Setting PowerBI up to handle executing our Python code happens automatically in that PowerBI will detect where we have Python installed.
However, if for any reason we need to help PowerBI to figure out which Python version to use, we can specify that in the settings. To get there:
- Click on the Py icon on the Visualizations pane
- Click on the Settings (wheel) icon to bring up the Options window
- Click on “Python scripting” to change the Python directory
Loading The Data
We can use PowerBI to connect to many data sources including spreadsheets or databases. A full breakdown of how to connect PowerBI to different data sources is far beyond the scope of this tutorial. We are going to demonstrate the example using a data source from an Excel file.
Essentially, we only need to select a file from the file selector that opens when we click “Excel workbook” on the ribbon.
Once we select a file, the “Navigator” window will pop up, and we can select which sheet within the Excel file we want to use.
We can click on “Transform Data” to bring up PowerQuery to do data transformation. Once we are okay with the data, simply click on the “Load” button to load the data into PowerBI.
It will then show up in the Fields pane on the far right as a listing of tables and their individual fields/columns.
Creating The Python Visual
Once our data is inside PowerBI, click on the “Py” icon on the Visualizations pane. PowerBI will ask us to “Enable” script visuals, just click on “Enable”.
Choose individual columns from our data source in the Fields pane and drag them to the empty visualization on our dashboard or the Values box at the bottom Note the visualization pane. The preamble code in Python script editor changes when we drag each field, specifically line 3 that builds a dataset, despite appearing to be commented out.
This dataset variable transfers data from PowerBI to Python and the editor gives us space to tweak the script for the final visual.
Pro tip: we may wish to debug your visual and get it looking right in a Jupyter notebook, outside of Power BI, and then copy/paste when done, as this editor is not the best place for fixing code when things go wrong.
Feel free to try the below script in PowerBI:
import seaborn as sns
import matplotlib.pyplot as plt
sns.catplot(x='Country', y='Amount', hue = "Product", kind = 'bar', data=dataset)
plt.show()Once the Python script is ready, we can run it with the play button at the top right corner of the Python script editor and see the results appear above instantly.
And that’s all there is to it! We just ran Python inside Power BI to create a custom visualization.
To recap the steps:
- Make sure we have Python, Pandas, and Matplotlib installed on our local machine
- Load data into Power BI
- Click on the Py visualization in the visualization pane
- Drag fields from the Fields pane to the Values box in the Visualizations pane or the empty visualization itself on the dashboard
- Create a chart with Python by writing code in the Python script editor
- Click the run button on the Python script editor
- Admire our visualization
Additional Resources
How to Make a WordCloud in Python
Python Data Visualization & Exploration With Plotly
About the Author
Eric Smith is a classically trained poet and self-taught programmer, studying Computer Information Systems at Boston University – Metropolitan. When he’s not working as a Data Scientist at a Fortune 500 company. Eric enjoys spending time with his wife, their two children, and four dogs.
You can find him online at: