Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric certified for FREE! Don't miss your chance! Learn more

datadonuts
Advocate II
10 hours ago

Automate Power BI Model Optimization: Best Practice Analyzer Meets Claude AI

Transform hours of manual model tuning into an intelligent, guided workflow using the Best Practice Analyser (BPA) in Tabular Editor and Claude AI

I recently came across a video and blog post from 2021 by Michael Kovalsky from the Power BI Customer Advisory Team that discusses how to enhance the performance of a Power BI semantic model using Tabular Editor 2. The post explains how to run a Best Practice Analyzer against your Power BI semantic model to identify weak points, particularly related to performance. The technique is solid, but there is one major drawback: manually fixing hundreds of violations is mind-numbing work.

Fast forward to today, and we now have a game-changer. The powerbi-modeling-mcp server for Claude Desktop can automate this entire process while keeping you fully in control.

 

The Challenge

Run the Best Practice Analyzer on any real-world Power BI model and you'll likely see something like this:

  • 47 performance violations
  • 89 DAX expression issues
  • 134 formatting inconsistencies
  • 28 maintenance warnings

Even a small sized model can surface 200+ recommendations across categories like:

 

Performance: Bi-directional relationships on high-cardinality columns, unpartitioned large tables, auto-date tables

DAX Quality: Division operators instead of DIVIDE(), IFERROR usage, improperly qualified column references

Formatting: Missing data categories, exposed foreign keys, unmarked primary keys

Maintenance: Unused columns, orphaned tables, unreferenced data sources

 

Clicking through each one to understand, fix, test, and validate takes a lot of time.

 

The Solution: Intelligent Automation using Claude Desktop MCP with Human Oversight

 

With Claude Desktop connected to your Power BI model via the powerbi-modeling-mcp server, you get an AI agent that understands Power BI semantics, can read your model structure, and systematically apply fixes—all while explaining each change and checking for potential issues. 

 

What You'll Need

Tabular Editor 2 (the free version—no need for TE3)
Claude Desktop with powerbi-modeling-mcp server configured 

datadonuts_0-1770495894304.png

 


Your Power BI model (.pbix or .pbit file) and a backup of your model (seriously—create one before starting)

 

Guides 

How to install and setup your MCP server see here: Get started with the remote Power BI MCP server - Power BI | Microsoft Learn and here: GitHub - microsoft/powerbi-modeling-mcp: The Power BI Modeling MCP Server, brings Power BI semantic ...

 

The Step-by-Step Workflow

1. Run the Best Practice Analyzer

Open your model in Tabular Editor 2:

  • Launch it from Power BI Desktop's External Tools ribbon, OR
  • Open your .pbix or .pbit file directly in Tabular Editor 2

Run the analyzer:

  • Press F10 or navigate to Tools → Best Practice Analyzer

datadonuts_1-1770495894276.png

 

  • Review the violations organized by category

datadonuts_0-1770497389546.png

 

 

  • Copy batches of violations e.g. the "Formatting" into an editor and save it.

    datadonuts_1-1770497389551.png

     

datadonuts_0-1770498708739.png

 

2. Connect Claude to Your Model

Open Claude Desktop and use this prompt structure:

 
Task: Optimize Power BI Semantic Model Using Best Practice Analyzer
Step 1: Connect to ModelConnect to the Power BI semantic model file: [path/to/your_file.pbix]
Step 2: Analyze and Apply ImprovementsReview the following Best Practice Analyzer violations and apply corrections sequentially.
Step 3: Safety FirstBEFORE applying any correction, evaluate:- Will this break existing calculations, relationships, or functionality?- Could this cause the model to fail or become unstable?
If potential issues are identified, skip that violation and request my approval before proceeding.
Step 4: Document EverythingLog each change: object name, property modified, old value, new value.

 

Claude will connect to your model and be ready to process violations. 

 

datadonuts_0-1770497494218.png

 

 

 

Guide

For a comprehesive Prompt guide you can download this prompt file from the attachement and adopt it for your use case.

 

3. Work in Controlled Batches

This is crucial: don't paste all 200 violations at once. Instead, work in logical groups:

Batch 1: All formatting violations (safest to start)
Batch 2: Simple DAX improvements (DIVIDE, qualified references)
Batch 3: Performance optimizations (hiding columns, marking keys)
Batch 4: Maintenance items (removing unused objects)

Copy one batch of violations from the BPA window and paste them into Claude. The agent will:

  • Analyze each violation
  • Evaluate the safety of the fix
  • Apply the correction using the MCP server
  • Document what changed and why
  • Flag anything that needs your review

 

4. Validate After Each Batch

After Claude processes a batch:

  1. Save in Tabular Editor (Ctrl+S)
  2. Open the model in Power BI Desktop
  3. Test your reports and key calculations
  4. Verify data refresh works (if applicable)

If something breaks, restore from your backup and proceed more cautiously with that specific type of change.

Note, that sometimes a refresh of your model is required.

 

Understanding the Rules

Want to know what a specific BPA rule actually does?

  1. In Tabular Editor, go to Tools → Manage BPA Rules
  2. Click "Rules for local user"
  3. Select any rule and click "Edit Rule"
  4. Review the description and reference documentation

Each rule includes an explanation of the best practice and why it matters—often with links to detailed Microsoft documentation.

 

Some Tips for Success

Start with formatting: These changes are typically safe and give you confidence in the process

Test incrementally: Never apply 50+ changes without validating between batches

Read the rules: If you don't understand why a rule exists, look it up before applying the fix

Version your backups: Create dated copies as you progress (Model_Jan30, Model_Jan31, etc.)

Review Claude's log: The agent documents every change—use this as your change record

You're the expert: Claude automates the mechanics, but you make the final call on complex scenarios

 

Pro Tip:

Create a project in Claude AI so you can use the BPA on each of your models during development just with a simple prompt.

 

Real-World Impact

Before: Manually reviewing and fixing 200+ violations across a production model = 4-6 hours of tedious point-and-click work

After: Claude processes batches in minutes while you review, validate, and make strategic decisions = 45-60 minutes of focused oversight. 

 

Hope that helps. Drop your questions in the comments. 

 

Resources

 

Labels:
0