I Automated My Power BI Commentary With Claude

Every month, I’d finish the dashboard before noon and spend the rest of the afternoon writing the explanation underneath it. The numbers were done. The visuals were clean. And then someone in the review meeting would ask, “But why is Hell’s Kitchen labor up?” And I’d type it out. “Why is Astoria above budget on revenue?” Type that out too. Three locations, six months of data, same conversation every cycle.

That’s the commentary problem. The dashboard shows the number. Somebody still has to explain it.

I fixed that with Claude Code and a few Python scripts. Now I run one command at the start of month-end and the Power BI commentary writes itself into the report. This article walks through exactly how I set it up, including a few things I had to figure out the hard way.

Why Power BI Commentary Never Gets Automated

Most Power BI automation advice is about the dashboard itself: DAX measures, Power Query transformations, report layouts. Those are genuinely useful, and there’s no shortage of tutorials on them.

Commentary is different. It requires someone to read the numbers, figure out what moved, understand why, and write a sentence about it. That’s always been treated as a human job. Not because it requires deep judgment (most variance commentary is pretty formulaic once you’ve written it a few times), but because nobody built the connection between “live Power BI data” and “AI writes the explanation.”

That’s the gap I filled.

My setup uses three tools working together. The Power BI CLI pulls data directly from my live report. Claude reads that data and writes location-level variance commentary. And a Python script drops the commentary into the Excel file my Power BI report is already connected to. Refresh the report, and the commentary card updates.

No Fabric license. No manual export. One command.

How Claude Fits Into The Picture

Before getting into the steps, it helps to understand what Claude is actually doing here.

Claude is an AI made by Anthropic. You can interact with it through a chat interface, but you can also call it programmatically through an API, which means you can build scripts that send Claude a prompt, get a response back, and do something with that response automatically.

In this case, the script sends Claude a table of actuals vs. budget numbers for a specific location and month. The prompt tells Claude to write 3-5 bullet points of variance commentary, name specific dollar amounts, and identify drivers where the data supports it. Claude returns the commentary as plain text, and the script writes it to the right row in my Excel file.

The key insight is that Claude writes much better commentary when you give it structured, specific data rather than asking it generic questions. The prompt design matters a lot, and I’ll cover exactly how I structured mine.

What you need before you start

This setup involves a few tools you may not have used before. Here’s what you need and what each one is:

Power BI Desktop or Power BI Service: You’re presumably already using this.

The Power BI CLI: A command-line tool made by Microsoft that lets you query Power BI datasets directly from your terminal, without going through the browser or clicking Export. Think of it as a remote control for your Power BI data. It uses npm (Node’s package manager) to install, which means you need Node.js on your machine. If you don’t have it, download it at nodejs.org. It takes about 2 minutes to install.

Python: The scripting language that glues everything together. Python 3.8 or higher works fine. If you don’t have it, download it at python.org.

The anthropic Python library: Anthropic’s official library for calling Claude from Python. You install it with pip, which comes with Python.

The openpyxl Python library: A library for reading and writing Excel files from Python. Also installed with pip. This is what writes Claude’s commentary back into your Excel file.

A Claude API key: You’ll need an account at console.anthropic.com. The API is pay-as-you-go, and the cost for a monthly commentary run is a few cents.

An existing Excel commentary file connected to your Power BI report: This is the file the script writes into. If you don’t have one yet, I’ll show you the structure it needs.

Step 1: Install the Power BI CLI and connect it to your workspace

Open your terminal. On Windows, that’s Command Prompt or PowerShell. On Mac, it’s Terminal.

Install the Power BI CLI by running:

npm install -g @microsoft/fabric-cli

The -g flag installs it globally, which means you can run it from any folder on your machine. After it finishes, confirm the installation worked by running:

pbi --version

If you see a version number, you’re good. If you get an error saying pbi isn’t recognized, restart your terminal and try again.

Next, connect the CLI to your Power BI account:

pbi login

This opens a browser window where you sign in with the Microsoft account that has access to your Power BI workspace. Once you authenticate, the CLI stores your credentials locally so you don’t have to log in again every time.

Now find your workspace. Run:

pbi workspaces list

This returns a list of every workspace your account can access, each with a unique ID. Copy the ID for the workspace that contains your report. It looks something like a1b2c3d4-e5f6-7890-abcd-ef1234567890.

Then list the datasets inside that workspace:

pbi datasets list --workspace YOUR-WORKSPACE-ID

A dataset in Power BI is the data model behind your report. It’s where all your tables, relationships, and measures live. Find the dataset that powers your P&L report and copy its ID.

Step 2: Pull your data with a query

This is where things get interesting. The Power BI CLI can run queries against your dataset and return the results as a file. The query language is DAX, the same language Power BI uses for measures and calculated columns. If you’ve written a calculated column or a measure before, you’ve used DAX. Here we’re using a simple form of it to pull a full table.

Run this command, swapping in your actual dataset ID:

pbi dataset run-query --dataset YOUR-DATASET-ID --query "EVALUATE 'GL Data'" --output gl_data.json

The EVALUATE part is the DAX. 'GL Data' is the name of the table you want to pull. Replace it with whatever your actual table is called. The --output gl_data.json tells the CLI to save the results as a JSON file.

Open gl_data.json and take a look at the structure. For a P&L dataset, you’ll typically see columns for location, month, account (Sales Revenue, Labor, COGS, etc.), version (Actuals or Budget), and the dollar value. In my case, this comes back as 216 rows covering six months for three locations.

If your table has different column names, note them down. You’ll need to reference them in the Python script in the next step.

A quick note on table names: If your table name has spaces, wrap it in single quotes in the query, like 'GL Data'. If it doesn’t have spaces, the quotes are optional.

Step 3: Set up the Python script

Create a new folder for your project. Inside it, create a file called commentary_pipeline.py. This is the script that takes the data from Step 2, calls Claude, and generates the commentary.

Install the Python libraries you’ll need:

pip install anthropic openpyxl

Here’s the structure of the script:

import json
import anthropic

# Load your data
with open("gl_data.json") as f:
    raw = json.load(f)

# Convert to a usable format and compute variances
# Group by location + month, separate Actuals from Budget
# Calculate: variance = Actuals - Budget for each account

client = anthropic.Anthropic(api_key="YOUR-API-KEY")
commentary_dict = {}

for location in locations:
    for month in months:
        variance_table = build_variance_table(location, month, data)
        
        response = client.messages.create(
            model="claude-opus-4-6",
            max_tokens=1024,
            messages=[{
                "role": "user",
                "content": build_prompt(location, month, variance_table)
            }]
        )
        
        commentary_dict[f"{location}_{month}"] = response.content[0].text

The API key goes in where it says YOUR-API-KEY. For security, it’s better practice to set it as an environment variable and call os.environ.get("ANTHROPIC_API_KEY") instead of hardcoding it, especially if you’re sharing the script with anyone.

The full script with the variance computation and table-building logic is in Finance AI Lab, where I’ve commented every section. The structure above is the skeleton. The real work happens in build_variance_table() and build_prompt().

Step 4: Write the commentary prompt

The prompt is the most important part. This is where you tell Claude what your data means and exactly what kind of output you want. The quality of the commentary is almost entirely determined by how well the prompt is written.

Here’s the prompt I use:

You are a financial analyst writing variance commentary for a multi-location business.

Here is the Actuals vs. Budget data for [LOCATION] in [MONTH] [YEAR]:

[VARIANCE TABLE]

Write 3 to 5 bullet points of variance commentary.
- Cover Sales Revenue, Labor, COGS, and any operating expenses with a notable variance
- Name the dollar amount and direction for each point (e.g., "$18,200 above budget")
- If there is a clear driver based on the data, name it
- Keep each bullet to one sentence
- Use plain language, no accounting jargon
- Do not use headers or labels

The [VARIANCE TABLE] placeholder gets replaced by the actual computed variance data before the prompt is sent. In practice it looks like:

Account           | Actuals   | Budget    | Variance   | % Var
Sales Revenue     | $526,351  | $490,000  | +$36,351   | +7.4%
Labor             | $187,200  | $169,000  | +$18,200   | +10.8%
COGS              | $142,800  | $147,000  | -$4,200    | -2.9%
Marketing         | $12,400   | $12,000   | +$400      | +3.3%

When Claude sees a table like that alongside the prompt instructions, the output is specific:

“Labor costs came in $18,200 above budget in May, driven by additional barista coverage and 2 unplanned sick day replacements.”

Compare that to what you get when you just paste in a raw table with no formatting instructions:

“Labor costs were higher than expected due to various staffing factors.”

Same data. Completely different output. The structure of the prompt is doing the work.

Step 5: Write commentary back to your Power BI report

This is the step that closes the loop. The script takes Claude’s commentary for each location and month and writes it back into the Excel file your Power BI report is connected to.

If you don’t already have an Excel commentary file wired up to your report, here’s the simplest way to set it up:

1. Create a new Excel file with three columns: Location, Month, Comment
2. Add one row for each location/month combination you want to cover
3. Leave the Comment column blank for now
4. Save the file somewhere your Power BI report can access it
5. In Power BI Desktop, go to Get Data, choose Excel, and connect to this file
6. Add a text card or table visual to your report that shows the Comment column, filtered to the selected location and month

Once that’s in place, the write-back script looks like this:

import openpyxl

wb = openpyxl.load_workbook("Commentary.xlsx")
ws = wb["Comments"]

for row in ws.iter_rows(min_row=2):
    location = row[0].value
    month = row[1].value
    key = f"{location}_{month}"
    if key in commentary_dict:
        row[2].value = commentary_dict[key]

wb.save("Commentary.xlsx")

The script opens the workbook, loops through every row, looks up the commentary for that location/month combination, and writes it into the Comment column. Then it saves the file.

After running the full pipeline, open Power BI Desktop and hit Refresh. The commentary card on your report will update with the new text.

Running the full pipeline

Once all the pieces are in place, running the whole thing is a single command:

python commentary_pipeline.py

That’s it. The script pulls the data, computes the variances, calls Claude for each location/month, writes the commentary to Excel, and you’re done. For a three-location P&L covering six months, the full run takes about 60 seconds.

You can also set this up as a scheduled task so it runs automatically at the start of each month-end cycle. That’s a separate step I cover in Finance AI Lab.

Prompt variations worth knowing

Once the base setup is working, the prompt is the easiest thing to adjust. A few variations I use regularly:

Executive summary format. Instead of bullets, a single short paragraph per location. Useful when the commentary is going into an email or an exec deck where bullets look out of place.

Write a 2-3 sentence executive summary for [LOCATION] in [MONTH],
covering the most significant variance and its primary driver.
Plain language, no jargon.

Material variances only. Add a threshold so Claude only comments on variances that actually matter. Useful when you’re presenting to someone who doesn’t want to read about a $300 utilities variance.

Only include line items where the variance exceeds 10% of budget.
Skip anything immaterial.

Tone adjustment. Claude will match whatever tone you ask for. “Write in a direct, concise style suitable for a CFO” produces very different output than “Write in a conversational tone suitable for a team update.”

The point is that you own the prompt, which means you own the output format. Copilot gives you one style. This setup gives you exactly what you design.

Common issues and how to fix them

The CLI command returns an empty JSON file. This usually means the table name in your DAX query doesn’t match the actual table name in your dataset. Open your dataset in Power BI Desktop, go to Data view, and check the exact table name. It’s case-sensitive.

Claude’s commentary is vague even with the prompt. The most common cause is that the variance table being sent to Claude isn’t structured clearly. Make sure actuals and budget are on the same row for each account, with the variance calculated explicitly. If Claude has to infer the variance from separate rows of raw data, the output quality drops significantly.

The Excel write-back saves but Power BI doesn’t update. Power BI caches data between refreshes. Make sure you’re hitting the Refresh button in Power BI Desktop (or triggering a scheduled refresh in the Service) after the script runs. If you’re using Power BI Service and the Excel file is on your local machine, you’ll need to use a gateway or move the file to SharePoint/OneDrive.

The script runs but some locations are missing from the output. Check that the location names in your Excel commentary file match the location names in your Power BI dataset exactly. A trailing space or a capitalization difference will cause a mismatch.

API key errors. If you’re getting authentication errors from the Claude API, double-check that your API key is correct and that your account has usage credits available at console.anthropic.com.

What this won’t do for you

It’s worth being clear about the limitations, because the last thing you want is to show up to a review meeting with AI-generated commentary that missed something obvious.

It can only explain what’s in the data. If Hell’s Kitchen missed budget because you lost a wholesale account that isn’t reflected anywhere in the GL data, Claude won’t know. The data shows a revenue variance. It doesn’t show the reason. You still need to add qualitative context that isn’t captured in the numbers.

It doesn’t write forward-looking commentary. Claude summarizes what happened relative to budget. If your commentary format includes guidance, forecasts, or outlook language, that part stays manual.

It doesn’t catch data quality issues. If there’s a coding error in the GL that inflates one account and deflates another, Claude will write commentary based on the wrong numbers. Garbage in, garbage out. Same as any other tool.

The script breaks if your data structure changes. If you rename a column in your Power BI dataset or reorganize your Excel commentary file, you’ll need to update the script to match. It usually takes five minutes to fix, but you have to catch it before running month-end.