Integrate Python Scripts in Power BI

[uzuntasgokberk]

Discover the synergy between Python and Power BI for advanced data analysis and visualization. Integrating these tools enhances data-driven decision-making by creating powerful, interactive visualizations. Learn how combining Python’s analytical capabilities with Power BI’s visualization features can provide deeper insights and improve efficiency in your data workflows.

Introduction

Welcome to the perfect guide on combining Python with Power BI! In this tutorial, you’ll discover how to install Python, integrate it into Power BI, use it in Power Query, extract and transform data, and create stunning visualizations using libraries like pandas, matplotlib, and seaborn. Whether you’re a data analyst or BI enthusiast, you’ll learn how this integration elevates your reporting game plus understand the limitations you should be aware of when deploying Python visuals in Power BI Service.

 

What is Python and Power BI and what are the benefits of using it?

Python is a popular programming language that is widely used for data analysis and scientific computing. Power BI is a data analysis and visualization tool developed by Microsoft. When used together, Python and Power BI can be used to create powerful and interactive data visualizations, allowing users to gain insights from their data more easily and make better data-driven decisions.

The sub-titles will consist of 6 stages:

Installing Python

Integrating Python into Power BI

Extracting data using Python

Using Python with Power Query

Visualizing data using Python

Limitations of using Python in Power BI

 

Installing Python

Downloading the latest version of Python, you can go to the Python website and click on the “Downloads” tab. Currently, the latest version of Python is 3.11.0. If the link you have currently is for the 3.11.0 version, then you already have the latest version of Python installed. Otherwise, you can use the link to download and install the latest version of Python on your computer.)

https://www.python.org/downloads

After downloading the latest version of Python, you can open the terminal (or command prompt) and upgrade pip, which is the package manager for Python. This is important because without upgrading pip, you will not be able to download the latest versions of the libraries that you will be using. To upgrade pip, you can use the following command:

python -m pip install –upgrade pip 

The next step is to install the necessary libraries for using Python for data visualization in Power BI. Two of the most commonly used libraries for this purpose are matplotlib and pandas. Pandas is a powerful library for data manipulation and analysis, matplotlib is a popular library for creating data visualizations.

To install these libraries, you can use the following commands in the terminal (or command prompt):

pip install matplotlib
pip install pandas

In addition to these libraries, you may also need to install the openpyxl library in order to read Excel files (with the .xlsx, .xlsm, .xltx, or .xltm file extensions) using Python in Power BI. You can install this library using the following command:

pip install openpyxl

In this tutorial, we will also be using the seaborn library for creating data visualizations. You can install this library using the following command:

pip install seaborn

Once you have installed these libraries, you can start using them in your Python code to perform data analysis and visualization tasks in Power BI.

 

Integrating Python into Power BI

To open Power BI Desktop, navigate to the Python scripting page by going to File > Options and settings > Options > Python scripting in Power BI Desktop. On this page, you can specify your Python path in the “detected Python home directories” section, specify the IDE you will use in the “detected Python IDEs” section, or set the default .py files.

Up to this point, we have completed the process of integrating Python and Power BI. We have installed the necessary libraries for using Python for data analysis and visualization in Power BI, and we have enabled Python support in Power BI Desktop. We are now ready to start using Python in our Power BI reports and dashboards to perform data analysis and create interactive data visualizations.

 

Extracting data using Python

To create a new Python script data source in Power BI, you can go to the “Home” tab and select “Get Data” from the menu. In the “Get Data” menu, select “Python script” to create a new Python script data source. This will open a new editor window where you can write and run your Python code.

After creating a new Python script data source in Power BI, you can write your Python code in the editor window. Before writing your code, you need to import any libraries you will be using, such as pandas for data manipulation and analysis.

Here is the demo bike preview table:

Getting data with Python Script:

import pandas as pd
with open("C:\\Users\\extUzuntas\\AppData\\Local\\localdata\\bike.xlsx","rb") as file:
   bike = pd.read_excel(file,comment='#', header=2)
bike

 

Using Python with Power Query

Using Python and pandas with Power Query in Power BI, you can create a new “Python script” data source and write your code to clean and transform your data. Once your code is ready, you can go to the “Transform” tab in the Power Query editor, and select “Run Python Script” from the menu. This will open the Python script editor, where you can paste your Python code and run it to transform your data.

Python Script which is using in Power Query:

import pandas as pd
import numpy as np
dataset.drop("Owner Car Brand",axis=1)
dataset.set_index("Regıon",inplace=True)
dataset.drop(["Lord Of The Ring"],axis=0,inplace=True)
dataset.reset_index(inplace=True)
dataset["Regıon"] = dataset["Regıon"].fillna("Europe")
dataset['year'] = pd.DatetimeIndex(dataset['dteday']).year
dataset["dteday"] = pd.to_datetime(dataset["dteday"])
dataset['weathersit_expand'] = np.where(dataset['weathersit']==1,'Clear',np.where(dataset['weathersit']==2,'Mist + Cloudy','Light Snow'))
dataset

 

Visualizing data using Python

After using Python and pandas to clean and transform your data in Power Query, you can use the resulting data in your Power BI report or dashboard to create interactive data visualizations. To do this, you can go to the “Visualizations” pane in Power BI and select the “Python visual” option.

This will add a new Python visual to your report or dashboard, where you can write and run your Python code to create data visualizations using the transformed data from Power Query. You can use various libraries, such as matplotlib or seaborn, to create different types of visualizations, such as line charts, bar charts, or scatter plots.

Example of Scatter and Join Plot Python Visualization:

Scatter Python Script:

import seaborn as sns
import matplotlib.pyplot as plt
plt.figure(figsize=(10,6))
dataset.rename(columns={'temp':'Temperature','windspeed':'Windspeed'},inplace=True)
sns.relplot(x='Temperature',
           y='Windspeed',
           data=dataset,
           kind='scatter',
           hue='Regıon',
           style='Regıon',
           size='Temperature'
           )
plt.show()

Join Plot Python Script:

import seaborn as sns
import matplotlib.pyplot as plt
sns.set_palette('rainbow')
g=sns.jointplot(data=dataset,
           x='total_rentals',
           y='temp',
           kind='reg')
plt.show()

Example of Bar Chart, Weekday & Monthly Monitoring Python Visualization:

Bar Chart Python Script:

import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
fig, ax = plt.subplots()
ax=sns.barplot(data=dataset,x="Regıon",
               y="casual",
               estimator=np.sum,
               ci=0)
ax.bar_label(ax.containers[0])
plt.show()

Weekday & Monthly Monitoring Script:

import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
df_crosstab = pd.crosstab(dataset['mnth'],
                       dataset['weekday'],
                       values=dataset['total_rentals'],
                       aggfunc='mean')
sns.heatmap(df_crosstab,
           annot=True,
           cmap='coolwarm',
           cbar=False,
           linewidths=.7,
            fmt='.0f')
plt.show()

Example of Correlation Python Visualization:

Correlation Python Script:

import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
plt.style.use("default")
plt.figure(figsize=(10,6))
df_corr = dataset.corr()
mask = np.triu(np.ones_like(df_corr,dtype=np.bool))
sns.heatmap(
           dataset.corr(),
           mask=mask,
           linewidths=1,
           annot=True,
           cmap="coolwarm")
plt.show()

 

Limitations of using Python in Power BI

Personal Gateway is required for PBI Service.

Report Server does not support Python Script.

If the data is pulled through Python script, the data type of the columns you need in the script is not changed. This must be done in Power BI.

There are a limited number of Python libraries supported by Power BI Service. (Matplotlib, NumPy, Pandas, Scikit-learn, Scipy, Seaborn, Statsmodels, XGBoost)

When the Python icon is selected, the necessary columns must be placed in the values area.

The libraries you will use for your Python visualization must be written in the script, and for the visualization to work successfully, the script must have the plt.show() script at the end.

If the column names in the values field are changed later, the Python visualization will get an error.

Python visualizations require a Power BI Pro or Per User (PPU) license to use, refresh, filter, and cross-filter in reports.

The data used by Python visualizations is limited to 150,000 rows. If more than 150,000 rows are selected, only the top 150,000 rows are used and a message is displayed on the image. In addition, there is a limit of 250 MB on input data.

If a column in the input dataset of a Python Visual contains a string value longer than 32766 characters, this value will be truncated.

Python visuals are displayed at 72 DPI.

If a Python visual calculation takes more than five minutes, it will time out and result in an error.

If data fields from different tables without a defined relationship are selected, an error will occur as in Power BI Desktop visuals.

Python graphics are updated for data updates, filtering, and highlighting, but the image itself is not interactive and does not serve as a source for cross-filtering.

 

Conclusion

Integrating Python into Power BI expands your ability to clean, analyze, and visualize data using code-driven or visual techniques. Despite some limitations, this fusion empowers analysts and developers to build more flexible, deeper data stories. If you’re aiming to take your BI dashboards to the next level Python integration is a must-learn skill.