3 Easy Ways To Use Python In Power BI

I don’t know about you, but the first time I realized Microsoft Power BI—a leading data analysis tool—could run Python, I had that moment every finance pro gets when they discover a shortcut that would’ve saved them five years of their life. Like when I learned you could double-click the fill handle instead of dragging it down 40,000 rows. Or when I realized SUMIFS existed and immediately questioned what else Excel had been hiding from me for a decade.

Power BI + Python is that feeling… but dialed up.

Most finance teams still use Microsoft Power BI like it’s a prettier version of Excel charts—which is fine, but also a little like buying a Tesla because you like the cupholders. You’re ignoring the autopilot.

Because here’s the truth: Once you bring Python into Power BI, the ceiling disappears. Before you can use Python in Power BI, you’ll need to download Python from the official website and install it on your machine.

With Python, Power BI can:

• Clean the messiest data dumps with three lines of Pandas
• Run ML models that flag sketchy GL entries or forecast revenue
• Build custom visuals the native chart library can’t touch
• Generate features (rolling averages, anomaly scores, clusters)
• Automate preprocessing outside of Power BI so datasets arrive pre-scrubbed
• And—possibly my favorite— let you offload all the coding to ChatGPT and pretend you wrote it yourself.

This guide is my no-BS walkthrough of everything I wish I had when I first started mixing Python with Power BI. If you’re a finance pro drowning in manual transformations, wrestling with dirty data, or trying to squeeze more juice out of your dashboards, this is the playbook.

The Three Ways You Can Use Python in Power BI

Before we get into the fun stuff—like turning your ERP’s data dumpster fires into clean, well-behaved tables—let’s get one thing straight:

Power BI doesn’t just “support Python.” Python integration is a key feature of Power BI, allowing you to run Python scripts for data sourcing, transformation, and visualization directly within your reports.

It gives you three completely different doors into Python… and each one solves a different kind of finance headache. Integrating Python with Power BI can significantly enhance your analytics capabilities by streamlining workflows and enabling advanced data analysis and custom visualizations.

If you only use one of them, it’s like signing up for a gym membership and only using the vending machine.

Let me break down the full menu.

Python in Power Query (Transform Data → Run Python Script)

This is the big one. The workhorse. The place where 80% of your pain disappears.

Power Query normally uses M, which—let’s be honest—feels like someone duct-taped Excel formulas to a JavaScript tutorial. It’s powerful, but reading M code is like reading a ransom note made of curly braces.

Python in Power Query changes that.

Suddenly, you can:

• Clean your data with pandas, not nested M functions
• Generate new columns with real logic, not wizard UIs
• Merge datasets in one line
• Apply complex business rules
• Build engineering-level transformations right in your model
• Reuse code you already built for forecasting, anomaly detection, etc.

Python excels at data manipulation and advanced data transformations within Power BI, leveraging familiar data structures like pandas DataFrames. This allows you to import, process, and analyze data more flexibly and efficiently than with native Power BI features.

If your finance data is messy (spoiler: it is), this is where Python becomes your best friend.

Typical Power Query Python Jobs

• Cleaning ERP dumps
• Normalizing file structures
• Fixing dates that were apparently formatted by someone who hates you
• Adding calculated metrics (rolling averages, weighted metrics, ratios)
• Joining 3–7 datasets without losing your mind
• Running ML logic and adding new features

Think of it as SQL + Excel + Pandas had a very nerdy baby.

Python Visuals (Report View → Python Visual)

Now this one is flashy.

Where Power Query Python is the janitor who quietly fixes everything, Python visuals are the extrovert who walks into your dashboard wearing a velvet blazer and offering unsolicited insights.

A Python visual gives you:

• A pandas DataFrame of your filtered data
• A blank canvas
• And unlimited power to build charts that Power BI will never build natively

You use the python script editor in Power BI to write and edit your Python code for these visuals. By leveraging popular python libraries like Matplotlib and seaborn, you can create advanced data visualizations and visualise data in ways that go far beyond Power BI’s native capabilities.

This is where you create visuals like:

• Anomaly scatterplots
• Forecast charts with confidence bands
• Regression lines that actually look good
• Violin plots (a fancy way to say “your distribution is weird”)
• Cluster plots from k-means
• Density maps
• Custom financial visuals you’ll never find in the marketplace

And the best part? When a user slices or filters the page, your Python script reruns automatically using the filtered dataset.

It’s basically a tiny, embedded data science engine.

Warning: the visuals are static images, so no interactivity. But for analytics-heavy dashboards, they’re gold.

External Python Scripts Feeding Power BI (Automation + ML + APIs)

Most people never think about this one because it happens outside Power BI… but this is where things get dangerous (in a good way).

External Python scripts let you:

• Run heavy data prep before Power BI even opens
• Automate nightly transformations
• Pull data from APIs or scrape websites
• Run ML models offline
• Dump clean, enriched files into OneDrive or a data lake
• And let Power BI simply pick up the results as a data source through managed data connections

When automating data refreshes, you’ll need to manage your data connections and specify the correct data source for Power BI to ingest the output from your Python scripts. For scheduled refreshes involving Python, using a personal gateway is required, though it has some limitations compared to the enterprise gateway.

This is how you start operating like a real analytics team:

1. Python script fetches → cleans → enriches → saves a dataset
2. Power BI refreshes the model using those clean outputs
3. You get dashboards that “just work” every morning
4. Your team thinks you sold your soul to make month-end easy

This route is amazing when:

• You need scheduled automation
• Your transformations are heavy
• Your ML models take real compute
• Or your data sources are external

It’s the backbone of scalable, professional finance analytics.

So Which One Should You Use?

Honestly? All three.But for different reasons:

Python Method	Best For	Why
Power Query Python	Cleaning + prepping data	Eliminates 90% of the painful stuff
Python Visuals	Custom charts + analytics	Let’s you run “data science inside a visual”
External Python Scripts	Automation + ML + APIs	Turns Power BI into a hands-free reporting machine

Using scripts in Power BI—whether for data prep, custom analytics, or automation—lets you automate and enhance analytics workflows by integrating Python code directly into your reports.

If Power BI is the engine, Python is the turbocharger.

Python in Power Query — Where the Real Time Savings Happen

If Power BI had a backstage area where all the messy, embarrassing data cleanup happened before the polished dashboard took the stage, Power Query is that backstage—and Python is the grizzled veteran stage manager who’s been doing this longer than you’ve been alive.

Most finance pros never even open the Python door inside Power Query… which is wild, because this one feature alone can turn a 2-hour data-prep ritual into a 20-second refresh.

When you use Python in Power Query, the input data from your query is provided to the Python script as a DataFrame, making it easy to process, clean, and transform your dataset before it moves on to your Power BI reports.

Let’s break down exactly how it works and why it’s the single biggest unlock in Power BI for finance teams.

Why Use Python Instead of M?

I’ll be real with you: I respect M… but I do not love M.

M is powerful, but:

• It’s verbose
• It’s picky
• It makes you wrestle with every curly brace
• And debugging M at 11 PM before a board deck is a spiritual experience I wouldn’t recommend to anyone

Python, on the other hand, is:

• Fast
• Clean
• Readable
• Packed with a wide range of software libraries and Python packages (like pandas and Matplotlib) built for analytics
• Used everywhere outside of Power BI (so you can reuse code!)

And most importantly:

Pandas was practically built for the type of cleanup finance people do every single day.

If you’ve ever:

• cleaned a GL dump
• normalized revenue files
• mapped cost centers
• merged forecasts + actuals
• built custom fiscal calendars
• handled “creative” date formats
• dealt with empty columns named “Column1”… “Column2”… “Column2.1” (???)

Then Python in Power Query is a gift. With pandas, you can not only clean and transform messy data, but also perform advanced data analysis directly within Power Query, making it easier to extract insights and enhance your reporting workflows.

Setup — The 5-Minute “Plug Python In” Checklist

To configure Python for use in Power BI, first open Power BI Desktop to access the necessary settings.

Here’s the quick setup flow (you only do this once):

1. Install Python on your local machine. For best compatibility with Power BI, use the official Python distribution from python.org rather than alternative Python distributions like Conda or Anaconda. A local Python installation is required for scripting to work properly.
2. Install the required Python libraries (such as pandas and matplotlib) using your local Python installation.
3. In Power BI Desktop, go to File > Options and settings > Options. On the left menu, select Python scripting to open the Python script options page. Here, you can specify the local Python installation path. Power BI may display detected Python home directories—choose the correct one for your setup. This ensures Power BI uses the right Python environment for running scripts.

Once these steps are complete, you’re ready to use Python scripts in Power BI for data import, transformation, and visualization.

1. Install Python

If you don’t already have it:

• Download Python and install Python 3.x from python.org to enable script execution and data import in Power BI Desktop
• Or install Anaconda, which bundles all the libraries

2. Install core packages

You will almost always need:

• pandas (your new best friend)
• numpy (math stuff)
• matplotlib (for visuals if needed)
• scikit-learn (ML models)
• seaborn (fancy visuals)

You may also need to install additional Python libraries and Python packages, depending on your analysis requirements. Use pip to add any required software library, making sure they are supported by Power BI for scripting and visualization.

3. Tell Power BI where Python lives

In Power BI Desktop:
File → Options & Settings → Options → Python scripting

On the Python script options page, select Python scripting from the menu. Here, you can specify your local Python installation path. Power BI will display detected Python home directories, allowing you to choose the correct environment. Make sure to select the appropriate Python interpreter and configure the necessary packages for seamless integration.

4. Restart Power BI

(Yes, you really do have to.)

5. You’re ready.

Now every dataset can be passed directly into Python as a pandas DataFrame.

Walk-Through #1: Cleaning an ERP Export with Pandas

Let me give you the stereotypical finance example:

Your ERP gives you a GL export, typically as a csv file, which serves as the input data for your script. This export includes:

• four date columns
• blank rows
• header rows inside the data
• random footers
• text that should be numbers
• numbers that should be text
• and columns named “Column1”… “Column62”

A classic.

With Python in Power Query, you can fix all of it in one shot.

Step-by-Step

Step 1 — Load your raw table into Power Query

Start by connecting to your data source, such as a CSV file, database, or API. The input data from this data source will be loaded into Power Query. Doesn’t matter how ugly it is. Power BI will pass the input data to Python as-is.

Step 2 — Add a Python transformationIn Power Query: Transform → Run Python Script

You’ll see a blank editor.

Step 3 — Write (or paste) your pandas cleanup code:

Here’s an example I use all the time:

import pandas as pd

df = dataset.copy()

# Remove blank rows
df = df.dropna(how='all')

# Fix mixed date formats
df['PostDate'] = pd.to_datetime(df['PostDate'], errors='coerce')

# Convert numeric-looking columns
num_cols = ['Amount', 'Debit', 'Credit']
df[num_cols] = df[num_cols].apply(pd.to_numeric, errors='coerce')

# Normalize column names
df.columns = df.columns.str.strip().str.replace(' ', '_')

# Remove footer rows
df = df[df['Account'].notna()]

df

Step 4 — Hit OK → Power BI shows you the transformed table

Every time you refresh, your raw ERP data gets cleaned like this automatically.

Why this matters

What used to be:

• 40 minutes of cleanup
• 10 minutes of rechecking
• 5 minutes of cursing

…is now:

• a single refresh button.

Walk-Through #2: Merging Multiple Data Sources via Python

Let’s say you’ve got:

• Actuals
• Budget
• Forecast
• Headcount
• Cost center mapping

When working in Power BI, managing data connections and specifying the correct data source is crucial, especially when you need to import data from multiple tables and merge them into one magical fact table.

Yes, you can do it in M. Yes, you can do it in DAX. Yes, you can do it in Power Query transforms.

But the second those tables don’t line up perfectly, you’re in for a fun afternoon.

Here’s how Python handles this:

import pandas as pd

df_actuals = actuals.copy()
df_budget = budget.copy()
df_forecast = forecast.copy()
df_mapping = mapping.copy()

# Normalize join keys
for df in [df_actuals, df_budget, df_forecast]:
    df['Account'] = df['Account'].astype(str).str.zfill(6)

# Merge everything
df = df_actuals.merge(df_budget, on=['Account','Dept'], how='left', suffixes=('','_Budget'))
df = df.merge(df_forecast, on=['Account','Dept'], how='left')
df = df.merge(df_mapping[['Dept','DeptName']], on='Dept', how='left')

# Calculate variance
df['Variance'] = df['Actual'] - df['Budget']

df

You go from five tables → one perfect table in seconds.

Walk-Through #3: Creating Advanced Calculated Fields

This is where Python absolutely destroys M.

Python scripts in Power Query enable advanced data transformations and data manipulation that go far beyond what native Power BI features offer.

Want a rolling 12-month metric?

df['Rolling12'] = df.groupby('Dept')['Actual'].rolling(12).sum().reset_index(0,drop=True)

Want weighted averages?

df['Weighted_Rate'] = (df['Amount'] * df['Rate']).sum() / df['Amount'].sum()

Want anomaly detection?

from sklearn.ensemble import IsolationForest

model = IsolationForest(contamination=0.02)
df['AnomalyScore'] = model.fit_predict(df[['Actual']])

Want clusters?

from sklearn.cluster import KMeans

kmeans = KMeans(n_clusters=3)
df['Cluster'] = kmeans.fit_predict(df[['Margin','Sales']])

Want a custom fiscal calendar with week numbers, quarters, and 4-4-5 logic? Python can generate an entire calendar table in under 10 lines.

This is the stuff Power BI simply cannot do natively without tears.

Best Practices for Python in Power Query

I’ve broken more models than I care to admit, so here’s what experience has taught me. These best practices are especially useful for data analysts working with Power BI and Python.

1. Keep transformations focused

You don’t want 300 lines of logic in one step.
Break problem → solve → output clean table.

2. Push heavy ML upstream

Lightweight? Fine.
Training big models? Do that externally.

3. Watch Power BI Service limitations

Power BI Service will NOT run your Python scripts. They only execute in Desktop.

If you want to schedule Python script refreshes in Power BI Service, you must use a personal gateway, which is required for automated data refreshes involving Python.

So if you’re publishing to the cloud, use Python to prep the data locally → then rely on Power BI refresh.

4. Lock your Python environment

Version mismatches = pain.
Use venv, Anaconda, or a company-standard environment.

5. Document everything

Future You will not remember why you wrote this:

df = df[df['Date'] > '2023-01-01']

Trust me.

Python Visuals — Where Power BI Starts Being an Analytics Lab

If Python in Power Query is the behind-the-scenes janitor cleaning up your data, Python visuals are the main-stage performers.They’re the part of Power BI that makes your dashboards look like someone hired a full-time data scientist… even if it was just you, alone, at 11 PM, bribing yourself with cold brew and leftover pizza.

To demonstrate how to create advanced data visualizations with Python in Power BI, we’ll use a sample dataset—just like the ones provided by libraries such as Seaborn or through example files—to show how you can import, explore, and visualise data directly within your reports.

This is the feature nobody teaches finance pros, and it’s a shame, because Python visuals are ridiculously powerful once you know how to use them.

Let’s break down exactly what they can do, how they work, and how to build some visuals that will make your CFO think you’re running Skunk Works out of your home office.

What Python Visuals Actually Are

A Python visual in Power BI is basically this:

Power BI gives your Python script a pandas DataFrame containing whatever fields the user dropped into the visual. The Python script editor generates the code that creates this DataFrame from the selected fields, so you don’t have to manually write the data binding code—making it easier for users with limited programming experience.

Your script processes that DataFrame.

Python returns a static image (PNG).

Power BI renders it on the canvas.

It’s simple but insanely flexible.

Under the hood:

• When a user filters, slices, or cross-highlights on the report → your Python script re-runs on the filtered input data, which is passed as a DataFrame to the script.
• You can import any plotting library installed on your machine (matplotlib, seaborn, plotly if you render static images).
• You can run machine learning on the fly (just note: keep it light, as input data size and memory limits may restrict processing).

What it is NOT:

• It’s not interactive like built-in visuals
• It doesn’t support animation
• It has a row limit (~150k rows before things get slow)

But for finance analytics?
This is more than enough.

When to Use Python Visuals Instead of Built-In Charts

Here’s the rule of thumb I use:

If you want to create advanced data visualizations or visualise data in ways that go beyond Power BI’s built-in charts, Python visuals are a powerful option. Python lets you script custom plots and integrate them directly into your Power BI reports, making it a flexible data analysis tool for unique or complex visualization needs.

If the chart you want requires statistics, custom plotting, or multi-layer logic → Python wins.

Because Power BI’s built-ins can’t easily do:

• Regression lines
• Confidence intervals
• Distribution plots (histogram, violin, KDE)
• Multi-layer scatter plots with shapes AND colors AND cluster grouping
• Forecast curves with uncertainty bands
• Control charts
• Density heatmaps
• Outlier detection overlays
• Custom financial charts (profit curve, contribution margin map, ROIC vs growth quadrants, etc.)

Python unlocks visuals that look like they belong in a quant hedge fund’s dashboard…
but inside your normal Power BI report.

Setup Steps (Only Takes 90 Seconds)

1. Make sure Python is installed

Same as earlier in Section 3.

2. Enable Python visuals

In Power BI Desktop:
File → Options → Python scripting → Check the python script options to ensure the correct Python environment and interpreter are selected, and set the Python home directory.

3. Add a Python visual to your report

In the Visualizations pane, click the Python icon.

4. Drag fields into the “Values” well

Power BI passes these directly to Python as a DataFrame called dataset.

5. Write your script

Power BI shows a Python script editor at the bottom, where you write and edit your Python code for the visual.

Write your code, hit Run… and a visual appears.

Walk-Through #4: Variance Anomaly Scatterplot

Let’s say you want to answer this question:

“Which departments are blowing their budgets, and which deviations are statistically unusual?”

Excel can’t answer that. Power BI can’t answer that. DAX definitely isn’t answering that.

But Python knows exactly what to do. In Power BI, you can use the following code snippet to generate a scatterplot that highlights budget deviations and identifies outliers.

Step 1 — Add a Python visual

Drop in fields:

• Dept
• Actual
• Budget
• Variance
• Date (optional)

Step 2 — Use this Python code:

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.ensemble import IsolationForest

df = dataset.copy()

# Calculate variance if needed
if 'Variance' not in df:
    df['Variance'] = df['Actual'] - df['Budget']

# Fit anomaly model
model = IsolationForest(contamination=0.05)
df['Anomaly'] = model.fit_predict(df[['Variance']])

plt.figure(figsize=(8,6))
sns.scatterplot(
    data=df,
    x='Budget',
    y='Variance',
    hue='Anomaly',
    palette={-1:'red',1:'blue'}
)

plt.title("Variance Anomaly Detection")
plt.xlabel("Budget")
plt.ylabel("Variance")
plt.tight_layout()
plt.show()

What you get:

A scatter plot that:

• Colors normal behavior blue
• Colors anomalies red
• Updates instantly when you filter by date, region, account, anything

That is real analytics baked into a finance dashboard.

Walk-Through #5: Rolling Forecast Chart With Confidence Bands

Let’s say you want:

• Actuals
• Forecast
• A machine-learning-based projection
• 80% and 95% confidence intervals

Power BI can’t do this natively.
Python does it in 15 lines.

Example Python code:

import pandas as pd
import matplotlib.pyplot as plt
import statsmodels.api as sm

df = dataset.copy()
df = df.sort_values('Date')

# Build time-series model
model = sm.tsa.statespace.SARIMAX(df['Actual'], order=(1,1,1))
results = model.fit(disp=False)

forecast = results.get_forecast(steps=12)
mean = forecast.predicted_mean
conf = forecast.conf_int()

plt.figure(figsize=(10,6))
plt.plot(df['Date'], df['Actual'], label='Actuals')
plt.plot(pd.date_range(df['Date'].iloc[-1], periods=12, freq='M'), mean, label='Forecast')

plt.fill_between(
    pd.date_range(df['Date'].iloc[-1], periods=12, freq='M'),
    conf.iloc[:,0],
    conf.iloc[:,1],
    alpha=0.2
)

plt.title("Rolling Forecast With Confidence Intervals")
plt.tight_layout()
plt.show()

Why finance teams love this

It does in seconds what FP&A teams spend 3 days building manually in Excel every month.

Walk-Through #6: Cluster Analysis Visual (Customer, SKU, Store, Region)

Want to group:

• SKU profitability
• Customer segments
• Store performance
• Sales reps
• Regions

…without becoming a data scientist?

Cluster analysis is your friend.

Python code:

import pandas as pd
import matplotlib.pyplot as plt
from sklearn.cluster import KMeans

df = dataset.copy()

# Select clustering features
X = df[['Sales','Margin']]

# Fit model
kmeans = KMeans(n_clusters=3)
df['Cluster'] = kmeans.fit_predict(X)

# Plot clusters
plt.figure(figsize=(8,6))
plt.scatter(df['Sales'], df['Margin'], c=df['Cluster'])
plt.xlabel("Sales")
plt.ylabel("Margin")
plt.title("Customer/SKU/Region Clusters")
plt.tight_layout()
plt.show()

Clusters update as you filter your dashboard.
Which feels like magic.

Best Practices for Python Visuals

After building more Python visuals than I care to admit, here’s what matters most:

Python visuals in Power BI enable interactive data analysis by allowing visuals to update dynamically based on user-selected filters, making data exploration more flexible and insightful.

1. Always preprocess data in Power Query

Your Python visual should be about the chart, not fixing text columns.

2. Keep visuals small

150k rows max before things get bogged down.

3. Stick to simple models

Isolation Forest = ✔
Neural networks = ✘ (unless you like watching your computer melt)

4. Standardize templates

Build a handful of reusable Python snippets:

• variance scatter
• rolling forecast
• regression line
• distribution plot
• cluster plot

Paste as needed.

5. Remember: visuals are static images

No hover, no zoom, no tooltips.
But slicing still works beautifully.

External Python Scripts Feeding Power BI

This is the part of Power BI that genuinely feels illegal the first time you use it.
Not because it is illegal, but because it lets you automate so much of your month-end grind that your coworkers will start asking if you hired an intern.

Here’s the big secret:

Power BI doesn’t need to do all the heavy lifting.
You can run a python script in Power BI to import data from external sources—like APIs, Kaggle datasets, or local files—automating the process of downloading, cleaning, and loading data before it even hits your dashboards. You can also run Python outside Power BI—on a schedule, in the cloud, on your laptop, wherever—and simply feed the cleaned, enriched, supercharged data straight into your dashboards.

This is how you go from “nice dashboard” to automated finance data pipeline.

Let’s break it down.

What External Python Scripts Let You Do (The Big Picture)

Running Python outside Power BI unlocks things that Power Query + Python visuals simply cannot do, like:

✓ Fully automated data refreshes

Pull GL, sales, headcount, or CRM data automatically every night.

✓ Heavy transformations Power BI shouldn’t handle

Massive merges
Machine learning
Time-series processing
API pulls
Web scraping

✓ Ensuring consistency by working with the same data

Maintain data integrity by using the same data across refreshes and transformations, ensuring your backups, snapshots, and visualizations are always aligned.

✓ Feeding Power BI perfectly clean files

Your dashboards become “just refresh”—no prep steps, no manual cleanup, nothing to edit.

✓ Running models without slowing Power BI down

You train ML models offline, then load scored data into Power BI.
This keeps your dashboards blazing fast.

✓ Creating a real finance data pipeline

ERP → Python → Clean Data → Power BI
A little bit of engineering, a whole lot of sanity.

The 5 Ways to Run External Python Scripts

You’ve got options. And none of them require being a “real developer.”

1. Windows Task Scheduler

The classic.
Runs a Python script at:

• 2:00 AM
• every hour
• every Monday
• or whenever you choose

Super easy to set up.

2. Azure Functions or Azure Automation

If you’re in a corporate environment, this is the “real” solution.
Schedule Python in the cloud → drop output to a Storage Account or OneDrive → Power BI refreshes.

3. n8n (my personal favorite low-code platform)

You can:

• pull data
• run Python
• process files
• and upload to OneDrive/SFTP/SharePoint
  automatically.

n8n + Python = tiny finance automations that run themselves.

4. Zapier / Make.com with Python steps

If you’re already using these tools:

• schedule a run
• hit an API
• clean the data with Python
• push file to SharePoint or Google Drive for Power BI

Easy.

5. Hosted notebooks (Databricks, Google Colab, JupyterHub)

Write your Python logic in a notebook → schedule it → save output into your Power BI folder.

This is how you handle BIG datasets.

Walk-Through #7: Nightly GL Cleanup Script That Feeds Power BI

Let’s say you’re stuck with a nightly GL dump from your ERP that looks like it was formatted by someone with a personal vendetta against whitespace.

Python can clean the entire thing automatically—hours before you even wake up.

Flow:

1. Python script downloads the ERP dump from SFTP
2. Cleans it with pandas
3. Normalizes dates, chart of accounts, cost centers
4. Flags suspicious entries
5. Saves a clean CSV/XLSX file into a OneDrive folder
6. Power BI refresh in the morning picks up the clean file

Example Script:

import pandas as pd
from datetime import datetime

df = pd.read_csv("/sftp/raw/GLdump.csv")

# Clean up messy fields
df = df.dropna(how="all")
df['PostDate'] = pd.to_datetime(df['PostDate'], errors='coerce')
df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce')

# Add anomaly flag
df['IsAnomaly'] = df['Amount'].abs() > df['Amount'].abs().mean() * 4

# Save clean table
df.to_csv("/OneDrive/Finance/GL_Clean.csv", index=False)

Now your Power BI data model always gets the good stuff.

Walk-Through #8: Python Forecast Model Feeding Power BI

Maybe you want an automated forecast that:

• runs monthly or weekly
• uses actual machine learning
• doesn’t melt Power BI

Here’s the move:

1. Build & train your model in Python
2. Score future periods
3. Save as a clean “Forecast” table
4. Load into Power BI
5. Map it to your visuals

Example (12-month ARIMA forecast):

import pandas as pd
import statsmodels.api as sm

df = pd.read_csv("Sales_History.csv")
df = df.sort_values("Date")

model = sm.tsa.statespace.SARIMAX(df['Sales'], order=(1,1,1))
results = model.fit()

forecast = results.get_forecast(steps=12).summary_frame()
forecast.to_csv("Forecast.csv", index=False)

Your dashboard now updates with fresh forecasts automatically.

Walk-Through #9: External Python Script That Hits an API and Feeds Power BI

Want:

• FX rates
• Commodity pricing
• SaaS usage metrics
• Competitor pricing data
• Market data
• HRIS pull

Python makes it easy.

Example: Pulling FX data

import requests
import pandas as pd

url = "https://api.exchangerate.host/timeseries?base=USD&symbols=EUR"
data = requests.get(url).json()

df = pd.DataFrame.from_dict(data['rates'], orient='index')
df.reset_index(inplace=True)
df.rename(columns={'index':'Date'], inplace=True)
df.to_csv("FXRates.csv", index=False)

Schedule this nightly → Power BI always has fresh FX.

Why Finance Teams Love External Python Pipelines

Because suddenly:

• Data prep is automated
• Forecasts update themselves
• ML models run without slowing down Power BI
• Dashboards refresh clean every morning
• All the repetitive garbage work disappears

It’s the kind of automation that turns a reactive finance team into a proactive analytics team.

Using ChatGPT to Write ALL the Python You Need for Power BI

Let me tell you something that would’ve saved me YEARS of pain if someone had said it out loud sooner:

You do NOT need to be “good at Python” to use Python in Power BI.You just need to be good at telling ChatGPT what you want.

I’m dead serious.

The combination of:

• Power Query
• Power BI visuals
• Python
• and ChatGPT

…turns you into a one-person analytics team—even if the last time you wrote code was that required college class where you had to make a turtle draw a square.

Before pasting your Python scripts into Power BI, you can use Visual Studio Code—a powerful and popular editor for writing and testing Python code efficiently.

I probably write 20% of my Python code myself these days. The other 80%? ChatGPT writes it, explains it, fixes it, and optimizes it.

Here’s exactly how I do it.

Why ChatGPT + Python + Power BI Is an Absolute Cheat Code

Python is powerful.
Power BI is powerful.
ChatGPT is powerful.

Put the three together and suddenly you can:

✓ Convert messy ERP data into a clean Pandas DataFrame

✓ Merge 5 datasets with weird join keys

✓ Build advanced visuals (regression, anomalies, clustering)

✓ Train ML models

✓ Fix error messages instantly

✓ Translate business logic (“allocate rent by headcount”) into working code

✓ Turn vague instructions into fully functional scripts

It’s like having a data engineer, analyst, and Python mentor sitting beside you murmuring:
“Yeah, I’ll handle this.”

My Go-To Prompts for Power BI Python Work

You don’t need magical prompts—just clear ones.

When setting up your workflow, you may need to use the following command to install packages or configure your Python environment for Power BI integration.

Here are the templates I use constantly.

1. “Write a Python script for Power Query that…”

Example:

Write a Python script for Power Query that cleans this dataset:

• fix mixed date formats
• convert amount columns to numeric
• remove blank rows
• normalize account codes to 6 digits
• output the clean DataFrame

ChatGPT will spit out clean, ready-to-paste code.

2. “Convert this M query into Python (Power Query script).”

Paste your M code.
It converts everything to pandas.

3. “Build a Python visual for Power BI that…”

Example:

Build a Python visual for Power BI that plots Actual vs Budget with color-coded anomalies.

ChatGPT writes the Matplotlib + pandas code for the visual.

4. “Fix this error message”

Paste both:

• Your code
• The Power BI error message

ChatGPT is freakishly good at this.

5. “Rewrite this script to be more efficient.”

It will vectorize operations, remove loops, simplify logic.

6. “Add ML logic to this script.”

Examples:

• add anomaly scores
• add clusters
• add forecasts

7. “Explain this code to me like I’m five.”

Pure gold when you didn’t write the script originally.

The Master Prompt: Teaching ChatGPT Your Data Model Once

This tip alone can cut hours off your month-end prep.

When I start working on a new dashboard, I give ChatGPT a prompt like this:

I am working with a Power BI dataset with these columns:

• Date
• Dept
• Account
• Actual
• Budget
• Forecast
• Headcount
• Region

Assume:

• Date is YYYY-MM-DD
• Amounts may be text
• Dept and Account are join keys

From now on, when I ask for Python scripts, write them assuming this schema unless I say otherwise.

Now ChatGPT acts like it has your model open in another monitor.

Every time you ask for a script, it tailors it to your structure.

Walk-Through #10: Using ChatGPT to Build a Python Visual

Scenario: You want a Python visual that shows:

• Monthly revenue
• A rolling 3-month average
• Anomaly markers
• Filter-aware behavior
• Clean formatting

Step 1 — Paste sample data structure:

Columns: Date, Revenue, Region, Product

Step 2 — Ask for the visual:

Build a Python visual for Power BI that shows:

• Revenue over time
• Rolling 3-month average
• Red dots for anomalies
• Uses seaborn styling

Step 3 — Paste into Power BI

ChatGPT typically returns something like:

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.ensemble import IsolationForest

df = dataset.sort_values('Date')
df['Date'] = pd.to_datetime(df['Date'])

# Rolling average
df['Rolling3'] = df['Revenue'].rolling(3).mean()

# Anomaly detection
model = IsolationForest(contamination=0.05)
df['Anomaly'] = model.fit_predict(df[['Revenue']])

plt.figure(figsize=(10,6))
sns.lineplot(data=df, x='Date', y='Revenue', label='Revenue')
sns.lineplot(data=df, x='Date', y='Rolling3', label='Rolling 3-Month Avg')

# Highlight anomalies
anom = df[df['Anomaly'] == -1]
plt.scatter(anom['Date'], anom['Revenue'], color='red', s=40, label='Anomalies')

plt.title("Revenue Analysis with Rolling Average and Anomalies")
plt.tight_layout()
plt.show()

Drop that into Power BI and boom—you’ve got an analytics-grade visual.

Walk-Through #11: Using ChatGPT to Build a Power Query Python Script

Scenario: You need to:

• Clean Actuals
• Merge Budget
• Merge Forecast
• Generate variance
• Remove null departments
• Output a final fact table

Step 1 — Paste sample rows or column list

Step 2 — Ask:

Write a Python script for Power Query that merges these tables, cleans the data, and outputs a final fact table with variance columns.

ChatGPT generates something like:

import pandas as pd

a = actuals.copy()
b = budget.copy()
f = forecast.copy()

# Clean numbers
for df in [a,b,f]:
    df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce')

# Merge
df = a.merge(b, on=['Dept','Account'], how='left', suffixes=('','_Budget'))
df = df.merge(f, on=['Dept','Account'], how='left')

# Variance
df['Variance'] = df['Amount'] - df['Amount_Budget']

# Drop blanks
df = df[df['Dept'].notna()]

df

Paste → Done.

ChatGPT for Debugging (the lifesaver)

When Power BI throws a cryptic error like:

“Error: ‘Date’ is not defined”

or

“ValueError: could not convert string to float”

Copy the whole thing.
Paste into ChatGPT.
Paste your code.
Add:

Fix this.

Nine times out of ten, it finds the bug in under 5 seconds.
It is absurdly good at this.

Case Study — How I Turned a Chaotic Month-End Into a Semi-Automated Finance Machine

Let me walk you through a real project that perfectly sums up why Python + Power BI + ChatGPT is the ultimate trio for finance teams drowning in messy data.

Before diving in, remember: when using Python visuals, you need to enable script visuals in each Power BI session. This step is required every time you start a new session, but once enabled, you can use Python scripts throughout that session.

Because theory is nice, but watching this stuff actually fix a miserable month-end process? That’s where the lightbulb goes off.

This is the exact workflow I used for a client who was spending 14–18 hours every month manually cleaning data, stitching files together, hunting down variances, and building commentary for leadership.

We cut that to under 3 hours—with better accuracy and richer insights.

Here’s how.

The Situation: A Month-End Dumpster Fire

You’ve probably lived this movie:

• ERP exports in five different formats
• “Actuals” file with six hidden header rows
• Budget in a spreadsheet built sometime during the Obama administration
• Forecast in a format that appears to have been developed by cryptographers
• Half the GL entries missing cost centers
• No anomaly detection, just “Huh, that looks weird”
• And a dashboard that breaks every time someone coughs near the dataset

Leadership wanted:

• faster reporting
• better explanations
• real insights
• AND a dashboard that wouldn’t break if someone accidentally renamed a column

Enter Python + Power BI.

Step 1 — Cleaning the Raw Data in Power Query Using Python

The ERP GL dump was—how can I say this delicately—
a war crime in CSV form.

So I dropped the raw table into Power Query and added a Python script that:

• removed 5 rows of random header junk
• fixed mixed date formats
• converted “Amount” columns from text to numeric
• padded account codes to 6 digits
• removed blank rows
• validated cost center formats
• flagged suspicious transactions

Example of the cleanup step:

import pandas as pd

df = dataset.copy()

df = df.dropna(how="all")
df['PostDate'] = pd.to_datetime(df['PostDate'], errors='coerce')

df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce')
df['Account'] = df['Account'].astype(str).str.zfill(6)

df['IsAnomaly'] = df['Amount'].abs() > df['Amount'].abs().mean() * 4

df

Every refresh → perfectly clean GL data.
No human hands required.

Step 2 — Merging Everything Into One Fact Table Using Python

The client had actuals, budget, and forecast all in different formats.
Mapping keys didn’t align.
Some accounts only existed in two of the datasets.

Classic.

Rather than write a novel in M or DAX, I fed all three tables into Python and merged them cleanly:

df = actuals.merge(budget, on=['Dept','Account'], how='left', suffixes=('','_Budget'))
df = df.merge(forecast, on=['Dept','Account'], how='left')

df['Variance'] = df['Actual'] - df['Budget']
df['ForecastVariance'] = df['Forecast'] - df['Actual']

This produced a single, bulletproof fact table with:

• actuals
• budget
• forecast
• two variance columns
• and validated data types across every column

This step alone cut 6–8 hours from their month-end process.

Step 3 — Running an External Python Forecast Model Weekly

Their old process was “finance manager manually adjusts forecast in spreadsheet.”
Meaning:

• nothing was consistent
• no statistical backbone
• lots of vibes, very little math

We built a simple ARIMA-based forecast in Python:

• ran weekly
• wrote output into a clean “Forecast_Auto.csv”
• Power BI automatically ingested it

Forecast script (simplified):

import pandas as pd
import statsmodels.api as sm

df = pd.read_csv("SalesHistory.csv")
df = df.sort_values("Date")

model = sm.tsa.statespace.SARIMAX(df['Sales'], order=(1,1,1))
results = model.fit()

fc = results.get_forecast(steps=12).summary_frame()
fc.to_csv("Forecast_Auto.csv", index=False)

Spend an hour up front →
never touch the forecast manually again.

Step 4 — Adding a Python Visual for Real Anomaly Detection

Leadership didn’t just want numbers, they wanted explanations.

So we built a Python visual that automatically:

• detected unusual spikes/dips
• highlighted them
• provided anomaly scores
• updated when users filtered by region/department/etc.

The visual code:

import pandas as pd
import matplotlib.pyplot as plt
from sklearn.ensemble import IsolationForest

df = dataset.copy()
model = IsolationForest(contamination=0.03)
df['Anomaly'] = model.fit_predict(df[['Variance']])

plt.figure(figsize=(8,6))
plt.scatter(df['Budget'], df['Variance'], c=df['Anomaly'], cmap='coolwarm')
plt.title("Variance Anomaly Detection")
plt.xlabel("Budget")
plt.ylabel("Variance")
plt.tight_layout()
plt.show()

Suddenly, their dashboard wasn’t just “showing numbers”…
it was explaining them.

Step 5 — Using ChatGPT to Write, Fix, and Optimize EVERYTHING

This team had zero Python skills.

So we used ChatGPT like a full-time analyst:

• converting messy business rules into pandas logic
• debugging scripts in seconds
• rewriting visuals to be prettier
• explaining what each line of code did
• optimizing slow transformations
• converting Excel formulas into Python

One of the most common prompts they used:

“Here is my Power BI dataset schema. Write a Python script that merges these tables and calculates these metrics…”

ChatGPT delivered a fully working script every time.

The Results: A Reinvented Month-End Close

After two weeks of setup, here’s what month-end looked like:

Before Python:

• 14–18 hours of manual cleanup
• 3–5 hours of variance hunting
• 2–3 hours fixing broken dashboards
• inconsistent forecast
• no anomaly detection
• stressed team
• reporting always late

After Python:

• 2 hours of validation
• Automated GL cleanup
• Automated merges
• Automated forecasts
• Power BI dashboard shows anomalies immediately
• Zero broken models
• Leadership gets cleaner insights

This team went from reactive…
to predictive.

From spreadsheet janitors…
to finance engineers.

From “we don’t have time for analysis”…
to “we finally understand what’s driving performance.”

When to Use Python, When to Use Power Query, and When to Use DAX

This is the section that saves you from becoming that person—the one who rewrites a simple SUMIFS in Python or tries to build an anomaly detection model in DAX and melts their laptop.

Each tool in Power BI has a lane. For example, the Power Query Editor is designed for data shaping and transformation tasks, letting you manage and apply Python scripts directly within your data ingestion workflow.

Once you know the lanes, you stop overengineering and start flying.

Here’s the simple breakdown:

The One-Sentence Rule for Each Tool

Let me give it to you straight:

Use Power Query

to shape data.

Use DAX

to analyze data.

Use Python

to supercharge data.

If a task doesn’t fit those buckets, you’re about to use the wrong tool.

Let’s unpack each role with real finance context.

When to Use Power Query (M) Instead of Python

Power Query is your data-shaping workhorse.
It’s perfect for:

✓ Renaming columns
✓ Removing nulls
✓ Splitting columns
✓ Filtering rows
✓ Basic joins
✓ Pivot/unpivot
✓ Adding lookup columns
✓ Standardizing formats
✓ Reshaping wide-to-long
✓ Creating “simple” calculated columns

If it’s basic cleaning or structural prep, Power Query wins because:

• it’s fast
• it’s built-in
• it works in the Power BI Service
• everyone on your team can understand it

Use Power Query when:

• A junior analyst will inherit the model
• The transformation is simple
• You need maximum reliability
• You want to keep your dataset fast to refresh

Don’t use Python for:

• Splitting “2024-Q1” into columns
• Removing duplicates
• Changing data types
• Renaming 12 columns

That’s Power Query territory.

When to Use DAX Instead of Python

DAX is your analytics engine.
It calculates numbers that change based on user interaction.

If you need:

• YTD
• rolling averages
• YOY
• percent-of-total
• dynamic measures
• time intelligence
• anything that responds to slicers

DAX wins every time.

Use DAX when:

• You need something dynamic
• A calculation depends on filter context
• It’s a measure, not a column
• The result must update instantly

Don’t use Python for:

• YTD Actuals
• Running Total
• Margin %
• Monthly Variance %
• Ratio metrics
• KPI calculations

Python can do them…
but it shouldn’t.
That’s DAX’s job.

When to Use Python Instead of Power Query or DAX

This is the fun part.

Python is your heavy artillery.
Use it when the job is too big, too complex, or too statistical for the built-in tools.

Use Python when you need:

A. Advanced Data Cleaning

• fixing mixed date formats
• repairing malformed CSVs
• normalizing inconsistent schemas
• stitching together messy exports
• handling large datasets Power Query chokes on

B. Complex Merges or Business Logic

• multi-level joins
• reconciliation logic
• fuzzy matching
• conditional merges
• rule-based transformations

C. Machine Learning & Advanced Analytics

• anomaly detection
• clustering (KMeans)
• predictive modeling
• outlier scoring
• regression analysis
• feature engineering

D. Custom Visuals

• confidence intervals
• violin plots
• regression lines
• density heatmaps
• multi-layer scatter plots
• anomaly overlays

E. External Automation Pipelines

• scheduled data pulls
• web scraping
• API integrations
• offline forecast models
• pre-cleaning datasets before Power BI sees them

Python wins when the job requires:

• statistics
• advanced math
• automation
• machine learning
• real data engineering
• or when the built-in tools simply can’t do what you need

Side-by-Side Comparison (The Cheat Sheet)

Here’s the matrix I wish someone gave me years ago:

Task Type	Power Query	DAX	Python
Rename columns	✔	✘	Overkill
Basic cleaning	✔	✘	Overkill
Merge clean tables	✔	✘	✔ (if complex)
Fuzzy matching	✘	✘	✔
Dynamic KPIs	✘	✔	✘
Time intelligence	✘	✔	✘
Calculated columns	✔	✔	Overkill
Anomaly detection	✘	✘	✔
Forecast models	✘	✘	✔
Cluster analysis	✘	✘	✔
Automation (scheduled)	✘	✘	✔
Advanced visuals	✘	✘	✔
Heavy text cleanup	✘	✘	✔
Huge datasets	✘	✘	✔ (external preprocessing)

The 10-Second Decision Tree I Use on Every Project

Here’s the mental flowchart I run:

1. Is this a measure or something filter-dependent?
→ Yes = DAX
→ No = go to next

2. Is this simple cleaning or shaping?
→ Yes = Power Query
→ No = next

3. Does this require statistics, ML, automation, or advanced logic?
→ Yes = Python
→ No = Power Query

4. Does this need to be scheduled/run outside Power BI?
→ Yes = External Python script

After you do this a few times, it becomes instinct.