How to Use Python in Power BI

Introduction to Python Visuals in Power BI

Power BI provides an option to use Python scripts directly inside visuals. This feature allows you to leverage the data analysis, statistical, and visualization power of Python within Power BI dashboards.

When you add a Python visual from the Visualizations pane:

• Power BI passes the selected fields from your dataset into a pandas DataFrame named dataset.

• You can then write any Python code (for data cleaning, ML modeling, or visualization) inside the script editor.

• The output of the Python script (usually a chart or table) will appear as a Power BI visual.

 

What You Can Do with Python Visuals

1. Data Cleaning & Transformation

   • Handle missing values

   • Remove duplicates

   • Apply complex text processing
     Example: Standardizing addresses, cleaning customer feedback, encoding categories

2. Statistical Analysis

   • Correlation analysis

   • Regression models

   • Time-series forecasting
     Example: Predicting sales trends, analyzing seasonality

3. Machine Learning

   • Classification & clustering

   • Sentiment analysis

   • Anomaly detection
     Example: Predict whether customers will churn, group customers into segments

4. Advanced Visualizations (Beyond Power BI’s native charts)

   • Matplotlib, Seaborn, Plotly charts

   • Heatmaps, boxplots, violin plots

   • Word clouds, network graphs
     Example: Create a correlation heatmap of sales data or visualize customer reviews as a word cloud

5. Integration with External Models

• Import trained ML/DL models (Pickle, Joblib)

• Run predictions on Power BI data
  Example: Load a trained sentiment model and analyze live customer feedback

Why Use Python Visuals in Power BI?

✅ Extend Power BI’s built-in features with Python’s advanced analytics
✅ Create custom visuals that are not available in Power BI
✅ Combine business intelligence (BI) with data science (ML/AI) workflows
✅ Work with unstructured data (text, images) inside Power BI reports

Step-by-Step Guide: Using Python in Power BI 

---

Download Dataset 👉👉👉SampleSuperstore

1) Confirm Python is installed & find the exact path (VERY important)

1. Press Windows + R, type cmd, press Enter.

2. In Command Prompt, type: where python

1. • If installed you’ll see one or more paths, e.g.
     C:\Users\Admin\AppData\Local\Programs\Python\Python312\python.exe

   • Copy the exact path you want Power BI to use.

2. If nothing returns, install Python from python.org and then run where python again.

Tip: If you have multiple Python installs, use the one you’ll manage packages for.

2) Install required Python packages into the same Python you will point to

Open Command Prompt and run (replace path if you want to call pip via that python):

type this command : pip install pandas matplotlib seaborn

3) Tell Power BI where Python is

1. Open Power BI Desktop.

2. File → Options and settings → Options.

3. Under Global → Python scripting:

   • Detected Python home directories: choose one, or

   • Browse and paste the python.exe path you copied from where python.

4. Click OK.

4) Load your dataset into Power BI

1. Home → Get Data → Excel / Text/CSV → select SampleSuperstore file.

2. In Power Query (Transform Data):

   • Ensure numeric columns (Sales, Profit, Quantity, Discount) are Decimal Number or Whole Number.

   • Convert Postal Code to Text if you need it as categorical.

3. Click Close & Apply.

5) Add the Python visual to the report canvas

1. In the Visualizations pane (right), click the Python visual icon (Py / </>).

2. A blank placeholder appears on the canvas and a script editor shows at the bottom.

3. If you see an “Enable script visuals” prompt, click Enable.

6) Drag fields into the Python visual (CRUCIAL)

1. Select the blank Python visual (click it so it has a border).

2. From the Fields pane, drag the columns you need into the Values area for that Python visual. Example:

   • Category → Values

   • Sales → Values

   • Region → Values (only if you’ll use it)

3. Important: Power BI will pass ONLY the fields you drag to Python. Inside Python they will be available as a pandas DataFrame called dataset.

7) Understand the editor preamble (do not delete)

The editor shows an auto-generated commented preamble like:

# dataset = pandas.DataFrame(Category, Sales, ...)

# dataset = dataset.drop_duplicates()

# Paste or type your script code here:

• In Power BI Python Visuals, the variable dataset is automatically created by Power BI.

• It already contains the data you dragged into the Python visual’s Values area.

• That means you do not replace dataset with SampleSuperstore or any other dataset name.

👉 For example:

• If you dragged Category and Sales into the Python Visual, Power BI automatically builds dataset like this: it will creates the dataset automatically based on the variables dragged in values field.

Leave this alone. Write your code below that comment block. Use dataset in your code — do not try to read the CSV again.

Type the following code,  in script area and run the script.  You can find the column chart visual in report area

import matplotlib.pyplot as plt

# Group data

sales_by_category = dataset.groupby("Category")["Sales"].sum().reset_index()

# Plot

plt.figure(figsize=(6,4))

plt.bar(sales_by_category["Category"], sales_by_category["Sales"], color="skyblue")

plt.xlabel("Category")

plt.ylabel("Total Sales")

plt.title("Sales by Category")

plt.show()

1. plt.bar() → draws vertical bars (what Power BI users call a Column Chart).

2. plt.barh() → draws horizontal bars (what Power BI users usually call a Bar Chart).

If you want horizontal bars use following code

import matplotlib.pyplot as plt

# Group data

sales_by_category = dataset.groupby("Category")["Sales"].sum().reset_index()

# Plot plt.barh(sales_by_category["Category"], sales_by_category["Sales"], color="skyblue")

plt.xlabel("Total Sales")

plt.ylabel("Category")

plt.title("Sales by Category")

plt.show()