Power BI Module 9: Python Integration – Import, Clean & Visualize Data

Python Integration in Power BI

Power BI offers native support for Python, enabling powerful data processing, machine learning, and visualization capabilities. If you’re comfortable with Python and libraries like pandas, matplotlib, or seaborn, this integration will take your dashboards to the next level.

📦 Prerequisites: What You Need

• Python installed on your system (e.g., Anaconda or default installation)
• Python libraries: pandas, numpy, matplotlib, seaborn (install via pip)
• Configure Python in Power BI: File → Options → Python scripting

Note: Power BI Desktop currently supports Python 3.x (not 2.x).

🧩 Use Python as a Data Source

You can run Python scripts directly in Power BI to load data from custom sources, web scraping, APIs, or complex pre-processing pipelines.

1. Click Home → Get Data → More → Other → Python script
2. Paste your Python code (e.g., data loaded using pandas.read_csv())
3. Click OK to execute and preview the DataFrame
4. Select the resulting table and click Load

import pandas as pd
df = pd.read_csv("https://raw.githubusercontent.com/datablist/sample-csv-files/main/files/people/people-100.csv")

Use Case: Load web-scraped data or pre-cleaned CSVs using Python directly into your Power BI model.

🧼 Use Python for Data Cleaning (Power Query)

Power BI allows running Python scripts inside the Power Query Editor for advanced data wrangling.

1. Load a table into Power Query Editor
2. Click Transform → Run Python Script
3. Write Python code using the dataset object as input

# 'dataset' is the input table from Power BI
import pandas as pd

dataset['FullName'] = dataset['FirstName'] + ' ' + dataset['LastName']
dataset = dataset[dataset['Age'] > 30]  # Filter rows

Benefits:

• Use Python logic and syntax instead of M (Power Query Language)
• Apply complex conditions, string manipulation, joins, loops
• Reuse Python ETL code from Jupyter or standalone scripts

📊 Create Python Visualizations in Power BI

You can embed Python charts (matplotlib, seaborn, etc.) directly into Power BI report pages.

1. Drag the Python Visual from the Visualizations pane
2. Drag the fields you want to use into the Values pane
3. Power BI creates a pandas.DataFrame named dataset
4. Write Python code to create your visual

import matplotlib.pyplot as plt
import seaborn as sns

sns.barplot(x=dataset['Region'], y=dataset['Sales'])
plt.title("Sales by Region")
plt.xticks(rotation=45)
plt.show()

Note: Python visuals are static images (not interactive) and may take longer to render compared to native visuals.

🧠 Use Cases for Python in Power BI

• Web scraping: Pull live data from websites or APIs using requests and BeautifulSoup
• Machine learning: Train models using scikit-learn and embed predictions
• Data science: Perform clustering, regression, or NLP tasks within reports
• Advanced formatting: Generate unique visuals like violin plots or heatmaps using seaborn

⚠️ Limitations of Python in Power BI

• Visuals are static (unlike native Power BI visuals)
• Not available in Power BI Service unless using Personal Gateway
• Some enterprise environments restrict script execution
• Performance may slow down with large datasets or complex visuals

📌 Summary – Power Up Power BI with Python

• Use Python as a data source to pull, preprocess, or clean data dynamically
• Use Python in Power Query for advanced data transformation
• Use Python visuals to embed scientific or niche charts inside reports

🎯 Pro Tip: Use Python where Power BI’s native tools fall short — especially for web scraping, machine learning, or statistical analysis. Blend the best of both worlds!