Running Python Script in Power BI [Step-by-Step Guide]

Python is an advanced programming language used to generate and collect data. On the other hand, Power BI is useful for visualizing the data.

The combination of Python script and Power BI can be beneficial and bring revolution in everyday productive work.

In this article, I’ll provide the requirements and show how to run a Python script in Power BI.

So, let’s begin.

Quick Navigation show

Why Should You Use Python Script in Power BI?

You should use a Python script in Microsoft Power BI to enhance data manipulation and transformation, automate the data, extract data from websites or API, integrate machine learning, implement deep data analysis, and expand data visualization.

Here are the reasons to use Python scripts in Power BI:

Enhanced Data Manipulation and Transformation

Python has powerful data processing capabilities that can help you with complex data in Power BI. It can clean, manipulate, and enrich the data before starting analysis and preparing it for visualization.

Automation

Power BI can automate data using scripts and perform repetitive tasks efficiently. It saves time and resources on your local machine.

Custom Data Acquisition and Web Scraping

Python’s Matplotlib library provides powerful data visualization capabilities. You can create custom plots, charts, and graphs that are unavailable in Power BI’s default visualizations.

Machine Learning Integration

Power BI utilizes Python’s machine learning libraries to build and integrate machine learning models. It allows you to perform predictions, score new data, and acquire insights and relationships within your data.

Advanced Data Analysis

You can use a custom Python function to create and apply scripts to your Power BI data. It will give you advanced options for analyzing the data.

All these facilities greatly help the user to perform complex formulas in data to provide expected results quickly.

Prerequisite to Run Python Script in Microsoft Power BI

Python is an interpreted, high-level, general-purpose programming language. It is used widely by professional programmers and is also popular among beginners.

It has extensive use in collecting and visualizing data. Therefore, it works seamlessly with Microsoft Power BI.

The Power BI application has a dedicated setting for Python Scripting. But when you go to the option, you will see the location for the Python home directory is empty.

You may wonder why there’s no path showing for Python. The reason is you do not have Python installed on your local machine. Also, the required libraries for the MS Power BI are missing from your PC.

To run the Python script in Power BI, you need:

Python application
Pandas
Matplotlib

First, you need to install the Python program from the official website.

Here is the way to download and install Python on your Windows PC:

Navigate to Python’s official website using a browser.
Hover over Downloads and click on the version.
Install Python on your local machine.

If you use Windows, you can download Python directly from Microsoft Store.

Power BI Python integration requires Pandas and Matplotlib libraries to run correctly. You can install them using the Pip.

Pip is installed automatically with the Python version 3.8.x or later. You only need to install the Pandas and Matplotlib library using Command Prompt.

Follow the steps carefully to install Pandas and Matplotlib library using Command Prompt:

Open Command Prompt as administrator.
Type pip install pandas and press Enter to install the Pandas library.
Type pip install matplotlib and click Enter to install Matplotlib library.

Now, you need to enable the Python scripting correctly by following the procedures below to execute the program.

How to Enable Python Script in Power BI

To enable the Python script in Microsoft Power BI, you must specify the correct Python home directories. Also, you need to select the Python integrated development environment (IDE) path to launch the script on your local machine or Windows PC.

After installing the Python application on your computer, Power BI automatically detects the home directories in Python scripting settings. To verify the options, follow the procedures below.

Here are the steps to enable Python scripting in Power BI:

Launch Power BI and go to File.
Click on Options and settings and select Options.
Select the Python version from the Python home directories.
Choose the Default OS program for .PY files.

However, if you have multiple Python versions, you must choose the preferred one to execute the script.

Similarly, if you use Visual Studio Code, you can change the default OS program and select your preferred IDE for Python.

How to Run Python Script in Power BI

To run the script in Microsoft Power BI, you need to use the Python script option from Get data and input the code.

Note: Before starting to create the project, make sure to fulfill the prerequisite and enable the Python scripting from Settings.

Here are the steps to run the Python script in MS Power BI:

Press on Get data from the Home tab.
Click on Other and select Python script.
Press on Connect.
Paste the following sample code.

import pandas as pd
data = [['Alex',10],['Bob',12],['Clarke',13]]
df = pd.DataFrame(data,columns=['Name','Age'])
print (df)

Click on OK.
Check the box to show the table and press on Load.

The script will load, and Power BI will show the data as a table. You will find the column information in the Data pane on the right side.

The above example was a basic way to run the script using Power BI. Now, let’s work with more complex data and visualize them in the dashboard.

Follow the procedure below to visualize the Python script in Power BI:

Paste the following code in Power BI.

import pandas as pd
df = pd.DataFrame({
    'Fname':['Harry','Sally','Paul','Abe','June','Mike','Tom'],
    'Age':[21,34,42,18,24,80,22],
    'Weight': [180, 130, 200, 140, 176, 142, 210],
    'Gender':['M','F','M','M','F','M','M'],
    'State':['Washington','Oregon','California','Washington','Nevada','Texas','Nevada'],
    'Children':[4,1,2,3,0,2,0],
    'Pets':[3,2,2,5,0,1,5]
})
print (df)

Click on OK.
Select and Load the table.
Press on Python visual from the Visualization tab.
Click on Enable from the Enable script visual dialog box.
Drag the data and paste them into the Values.
Press on Run script.

After executing the script, an output will appear on the dashboard. You can drag to increase the window size.

Limitations of Power BI Python Script

The script can work as an advantage for the Power BI project. But it’s not free from limitations.

In a small script, it won’t bother you at all. But, when writing a large script and working with massive data, the limitations may hinder you from doing specific tasks.

Let’s learn more about the boundaries of script in Power BI from below.

Here are the limitations of the Power BI Python script:

Python visuals for plotting are limited to 150,000 rows, and the maximum data input size is 250 MB.
The maximum column string value is 32,766 characters.
Python visuals are restricted to 72 DPI.
Python visual will be timed out and show an error if the execution time exceeds five minutes.
You must define relationships with other tables to avoid errors.
Python in Power BI only supports Public sources.

While working on a Power BI Python script, you must consider the above limitations and work around them.