How to Import Python Script in Power BI

Running Python scripts directly inside Power BI opens up a world of possibilities for data analysis that go far beyond standard DAX and M code. If you know a little Python, you can perform complex data cleaning, apply advanced statistical models, or pull data from sources Power BI doesn't natively support. This guide will walk you through exactly how to set up and use Python in Power BI, covering everything from configuration to importing and transforming your data.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use Python in Power BI?

While Power BI's built-in tools are powerful, integrating Python gives you superpowers. You can tap into the massive ecosystem of Python libraries for data science and analysis.

• Advanced Data Manipulation: Use the Pandas library to perform complex data shaping, cleaning, and preparation steps that would be difficult or impossible in Power Query alone.
• Statistical Analysis: Leverage libraries like SciPy and Statsmodels to run statistical tests, regressions, and other analyses right on your data.
• Machine Learning: Integrate predictive models built with Scikit-learn or other machine learning libraries to add forecasting or classification capabilities to your reports.
• Custom Data Sources: Write scripts to fetch data from APIs, scrape websites with BeautifulSoup, or access databases that don’t have a standard Power BI connector.

In short, it bridges the gap between a business intelligence tool and a full-fledged data science environment.

Getting Started: Your Pre-Flight Checklist

Before you can start writing scripts, you need to have Python and a few essential libraries installed on your machine. This setup is often the biggest hurdle, but getting it right ensures everything else runs smoothly.

Step 1: Install Python

If you don't already have Python installed, head to the official Python website and download the latest version. During installation, it's critical that you check the box that says Add Python to PATH. This allows Power BI to find your Python installation automatically.

Pro Tip: If you are a developer and use multiple Python environments (like venv or conda), make sure the environment you want Power BI to use is the one recognized in your system's PATH. Power BI can get confused if it's pointed to an environment that's not active or doesn't have the necessary libraries.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Install Required Libraries

Power BI relies on a couple of key Python libraries to function. Open your command prompt or terminal and install them using pip, Python's package installer.

You absolutely need Pandas, which is the standard for data manipulation in Python. Most data exchange between Power BI and Python happens via a Pandas DataFrame.

pip install pandas

You'll also need a plotting library for creating visuals. Matplotlib is a common and reliable choice.

pip install matplotlib

Depending on your goals, you might also install other libraries now, like numpy, scikit-learn, or seaborn.

pip install numpy scikit-learn seaborn

Connecting Power BI to Your Python Installation

With Python and the necessary libraries installed, the next step is to tell Power BI where to find them. This only needs to be done once.

1. Open Power BI Desktop.
2. Go to File > Options and settings > Options.
3. In the Options window, scroll down to the Python scripting section under Global.
4. Power BI will try to auto-detect your Python home directory. If it finds it, you'll see the path listed under Detected Python home directories. If not, or if you want to use a specific installation (like one in a virtual environment), you'll need to set it manually.
5. Select Other and browse to the folder where you installed Python. The IDE setting is optional and can be left as is.
6. Click OK. Power BI now knows how to communicate with Python.

Method 1: Using Python as a Data Source

One of the most powerful uses of Python in Power BI is to create a dataset from scratch or pull from a source Power BI can't otherwise reach. This essentially turns your script into a custom data connector.

The key rule here is that your script must produce a Pandas DataFrame. Any DataFrames generated by your script will be presented in Power BI's Navigator as tables you can load.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide:

1. In Power BI Desktop, go to the Home ribbon and click Get Data.
2. In the Get Data window, search for "Python" and select Python script, then click Connect.
3. A Python script editor window will appear. Here, you'll write the code to generate your data.

Let's start with a simple example. We'll create a DataFrame from a Python dictionary.

import pandas as pd

# Creating a simple dataset
data = {
    'OrderID': [101, 102, 103, 104, 105],
    'Product': ['Gizmo', 'Widget', 'Doodad', 'Gizmo', 'Widget'],
    'Sales': [15.50, 22.00, 12.75, 16.00, 25.00],
    'Region': ['North', 'South', 'North', 'East', 'West']
}

# Converting the dictionary to a Pandas DataFrame
sales_df = pd.DataFrame(data)

# You can even add a new column
sales_df['Tax'] = sales_df['Sales'] * 0.08

1. After writing your script, click OK.
2. Power BI will execute the script. The Navigator window will then pop up, showing any DataFrames your script created. In this case, it will show sales_df.
3. Check the box next to sales_df and click Load. Your Python-generated data is now in Power BI, ready to be used in visuals just like any other data table!

Method 2: Running a Python Script in Power Query

You don't just have to use Python for importing data, you can also use it to transform data that's already loaded into the Power Query Editor. This is incredibly useful for data cleaning, feature engineering, or applying complex calculations that are easier to write in Python than in M.

When you run a Python script as a transformation step, Power BI takes the existing table in Power Query and automatically converts it into a Pandas DataFrame named dataset. Your script then manipulates this dataset DataFrame, and the resulting DataFrame is returned to Power Query.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Guide:

1. Load some data into Power BI from any source (e.g., Excel, SQL Server) and open the Power Query Editor by clicking Transform data.
2. Select the query (table) you want to transform.
3. Go to the Transform tab in the ribbon and click Run Python script. (You can also find this on the Add Column tab).
4. The Run Python script editor will appear. Notice the comment: `'dataset' holds the input data for this script.
5. Imagine we have a simple table of products with Product Name, Category, and Price. Let's use Python to create a new column called DiscountPrice where items in the "Electronics" category get a 10% discount.

# 'dataset' is a pre-defined DataFrame containing your Power Query data
import pandas as pd

# Create the new column by copying the original price
dataset['DiscountPrice'] = dataset['Price']

# Apply a 10% discount only to the 'Electronics' category
# The .loc method is used to select rows based on a condition
dataset.loc[dataset['Category'] == 'Electronics', 'DiscountPrice'] *= 0.90

1. Click OK. Power Query will execute the script on your data.
2. The result will be shown as a table value. Click on [Table] to expand it and see your original columns along with the new DiscountPrice column.

That's it! You've successfully used Python to apply a conditional transformation to your data.

Common Pitfalls and Best Practices

Integrating a programming language into a tool like Power BI can sometimes lead to unexpected issues. Here are a few things to keep in mind:

• Dependency Management: If your script uses a library that isn't installed in the Python environment Power BI is using, it will fail. Ensure all required libraries are installed by running pip install <library_name>.
• Path and Environment Issues: The most common error is Power BI not being able to find your Python installation. Double-check your paths in the Options menu. If you use virtual environments, make sure you point Power BI to the correct environment's directory.
• Performance: Python scripts can be slower than native Power Query transformations, especially on very large datasets. For multi-million row datasets, try to perform as much transformation as possible in the source (e.g., in SQL) or with native M code before resorting to Python.
• Debugging: Power BI's error messages for Python scripts aren't always very descriptive. It's a good practice to develop and test your script in a separate Python editor (like VS Code or Jupyter Notebook) with a sample of your data first. Once it's working there, copy and paste it into Power BI.
• Security and Sharing: Python scripts run on the machine viewing the report. If you publish a report that uses a Python script, anyone who wants to refresh the data will need Python and all the required libraries installed on their own machine. This is a crucial consideration for team collaboration.

Final Thoughts

Integrating Python elevates Power BI from a business reporting tool into a flexible and powerful analytics platform. You now know how to configure your environment and use Python scripts to both import new data sources and apply complex transformations within Power Query.

Setting up environments, managing libraries, and scripting custom solutions is a powerful skill, but it's also exactly the kind of time-consuming, manual data work that many teams struggle to keep up with. At Graphed, we built an entirely different approach. We eliminate the need to write code by letting you connect your marketing and sales data sources in seconds and use plain English to build real-time dashboards and reports. Instead of debugging scripts, you can simply ask, "show me website traffic from the last 30 days" and get an answer instantly. If getting fast, clear insights without the technical overhead sounds good, you should give Graphed a try.