Power BI MCP Server

An enterprise-grade Model Context Protocol server for Power BI

Enable AI assistants to interact with Power BI Desktop and Power BI Service through natural language.

---

Disclaimer: This is an independent, community-developed tool and is not affiliated with, endorsed by, or connected to Microsoft Corporation or Anthropic.

---

Overview

Power BI MCP Server bridges the gap between AI assistants and Microsoft Power BI, enabling seamless interaction with semantic models through the Model Context Protocol. Whether you're working with local .pbix files or cloud-hosted datasets, this server provides a unified interface for querying data, managing models, and performing bulk operations—all while maintaining enterprise-grade security.

Key Capabilities

Capability	Description
Dual Connectivity	Connect to both Power BI Desktop (local) and Power BI Service (cloud)
Natural Language Queries	Execute DAX queries through conversational AI
Bulk Operations	Rename tables, columns, and measures across your model
Security First	PII detection, audit logging, and configurable access policies
RLS Testing	Test Row-Level Security roles during development
Safe Refactoring	PBIP-based editing preserves report visual integrity

---

The V1 to V2 Journey: Challenges & Solutions

The Challenge: Bulk Renames Breaking Reports

In V1, we successfully connected to Power BI Service via XMLA endpoints. V2 introduced Power BI Desktop connectivity and write operations using Microsoft's Tabular Object Model (TOM). However, we encountered a critical limitation:

Problem: Renaming a table via TOM breaks all report visuals referencing that table.

Root Cause Analysis:

Power BI stores data in two separate layers:

+------------------------+     +------------------------+
|    Semantic Model      |     |    Report Definition   |
|    (Model Layer)       |     |    (Report Layer)      |
+------------------------+     +------------------------+
|  - Tables              |     |  - Visual bindings     |
|  - Columns             |     |  - Slicers & filters   |
|  - Measures            |     |  - Bookmarks           |
|  - Relationships       |     |  - Field references    |
|  - DAX expressions     |     |  - Formatting          |
+------------------------+     +------------------------+
         |                              |
         v                              v
    TOM can modify             TOM CANNOT modify

When you rename a table using TOM, it updates:

• The table name in the model
• DAX expressions in measures (with our auto-update logic)
• Relationship references

But it cannot update:

• Visual field bindings in report.json
• Slicer configurations
• Bookmark references

This causes visuals to show errors like "Can't find column 'OldTableName'[Column]".

The Solution: PBIP File-Based Editing

We researched Microsoft's own approach and discovered that even their tools face this limitation. The solution lies in the Power BI Project (PBIP) format—a text-based representation of both the model and report.

Two Report Formats Supported:

Format	Structure	Default
PBIR-Legacy	Single report.json	Until Jan 2026
PBIR-Enhanced	Individual visual.json files	From Jan 2026

MyReport.pbip
├── MyReport.SemanticModel/
│   ├── definition.tmdl          <- Model definitions (text)
│   └── definition/
│       ├── tables/*.tmdl        <- Table definitions
│       ├── cultures/*.tmdl      <- Linguistic schema (Q&A)
│       └── relationships.tmdl
└── MyReport.Report/
    ├── report.json              <- PBIR-Legacy: All visuals here
    ├── definition.pbir
    └── definition/              <- PBIR-Enhanced structure
        ├── report.json          <- Report-level settings only
        └── pages/
            └── [page_id]/
                └── visuals/
                    └── [visual_id]/
                        └── visual.json  <- Individual visual definition

Reference: Data Goblins - Programmatically Modify Reports

Our Implementation:

We built a dedicated PBIP Connector that:

1. Parses PBIP Structure - Locates all TMDL files and report.json
2. Updates Model Layer - Regex-based find-replace in TMDL files for:
   • Table definitions (table OldName → table NewName)
   • DAX references ('OldTable'[Column] → 'NewTable'[Column])
3. Updates Report Layer - JSON manipulation in report.json for:
   • Entity bindings ("Entity": "OldTable" → "Entity": "NewTable")
   • Query references ("OldTable.Column" → "NewTable.Column")
   • Native reference names

Result: Tables, columns, and measures can be renamed without breaking a single visual.

Lessons Learned

Challenge	Solution
TOM can't modify report layer	PBIP file-based editing
DAX references not auto-updating	Regex-based expression rewriting
No visibility into rename impact	scan_table_dependencies tool
Complex PBIP structure	Dedicated connector with path discovery

---

Architecture

┌──────────────────────────────────────────────────────────────┐
│                        AI Assistant                          │
│                  (Claude Desktop / Claude Code)              │
└─────────────────────────────┬────────────────────────────────┘
                              │ MCP Protocol
                              ▼
┌──────────────────────────────────────────────────────────────┐
│                    Power BI MCP Server                       │
│                        (34 Tools)                            │
├──────────────────────────────────────────────────────────────┤
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │  Security   │  │   Audit     │  │   Access Policies   │  │
│  │  (PII Mask) │  │   Logger    │  │   (Block/Mask)      │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
├──────────────────────────────────────────────────────────────┤
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │   Desktop   │  │    XMLA     │  │       PBIP          │  │
│  │  Connector  │  │  Connector  │  │     Connector       │  │
│  │  (ADOMD)    │  │  (Cloud)    │  │  (File Editing)     │  │
│  └──────┬──────┘  └──────┬──────┘  └──────────┬──────────┘  │
│         │                │                     │             │
│  ┌──────┴──────┐  ┌──────┴──────┐  ┌──────────┴──────────┐  │
│  │     TOM     │  │   REST API  │  │    TMDL + JSON      │  │
│  │  Connector  │  │  Connector  │  │      Parser         │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
└──────────────────────────────────────────────────────────────┘
         │                 │                     │
         ▼                 ▼                     ▼
┌─────────────┐    ┌─────────────┐       ┌─────────────┐
│  Power BI   │    │  Power BI   │       │    PBIP     │
│   Desktop   │    │   Service   │       │   Files     │
│ (localhost) │    │   (Cloud)   │       │   (.pbip)   │
└─────────────┘    └─────────────┘       └─────────────┘

---

Available Tools

Desktop Operations (7 tools)

Tool	Description
desktop_discover_instances	Auto-discover running Power BI Desktop instances
desktop_connect	Connect to a specific instance with optional RLS role
desktop_list_tables	List all tables in the connected model
desktop_list_columns	List columns for a specific table
desktop_list_measures	List all measures with their expressions
desktop_execute_dax	Execute DAX queries with security processing
desktop_get_model_info	Get comprehensive model metadata

Cloud Operations (6 tools)

Tool	Description
list_workspaces	List accessible Power BI Service workspaces
list_datasets	List datasets in a workspace
list_tables	List tables via XMLA endpoint
list_columns	List columns for a table
execute_dax	Execute DAX queries against cloud datasets
get_model_info	Get model info using INFO.VIEW functions

Security & Compliance (2 tools)

Tool	Description
security_status	View current security configuration
security_audit_log	Query recent audit log entries

Row-Level Security (3 tools)

Tool	Description
desktop_list_rls_roles	List RLS roles defined in the model
desktop_set_rls_role	Activate an RLS role for testing
desktop_rls_status	Get current RLS status

Model Modification via TOM (7 tools)

⚠️ WARNING: TOM-based rename tools are DEPRECATED for renaming operations!

The batch_rename_* TOM tools only update the in-memory model and DO NOT update report visuals. This causes broken visuals after rename. Use PBIP tools instead for all rename operations.

Tool	Description
scan_table_dependencies	Analyze impact before renaming
batch_rename_tables	⚠️ DEPRECATED - Use pbip_rename_tables instead
batch_rename_columns	⚠️ DEPRECATED - Use pbip_rename_columns instead
batch_rename_measures	⚠️ DEPRECATED - Use pbip_rename_measures instead
batch_update_measures	Bulk update measure expressions
create_measure	Create a new DAX measure
delete_measure	Delete an existing measure

PBIP Safe Editing (5 tools) ✅ RECOMMENDED

This is the correct way to rename tables, columns, and measures!

PBIP tools update everything: TMDL files, DAX expressions (with proper quoting), report visuals, and Q&A schema. Close Power BI Desktop before using, then reopen after.

Tool	Description
pbip_load_project	Load a PBIP project for editing
pbip_get_project_info	Get project structure information (detects PBIR format)
pbip_rename_tables	✅ Comprehensive rename: updates TMDL + DAX quoting + visual.json + cultures (all automatic)
pbip_rename_columns	✅ Rename columns (model + report layer, both PBIR formats)
pbip_rename_measures	✅ Rename measures (model + report layer, both PBIR formats)

PBIP Diagnostics (4 tools)

Tool	Description
pbip_fix_broken_visuals	Fix visual references after TOM/API rename (when rename was done outside PBIP tools)
pbip_fix_dax_quoting	Fix any remaining unquoted table names in DAX expressions
pbip_scan_broken_refs	Scan project for broken references - compare model vs visuals
pbip_validate	Validate TMDL syntax, find quoting issues, invalid references

Note: DAX quoting and visual updates are now automatically handled by pbip_rename_tables. The repair tools are only needed when renames were done outside of PBIP (e.g., via TOM API or Power BI Desktop).

---

Security Features

PII Detection & Auto-Masking

Sensitive data is automatically detected and masked before being returned to the AI:

Pattern	Example	Masked Output
SSN	123-45-6789	***-**-6789
Credit Card	4111-1111-1111-1234	****-****-****-1234
Email	john@company.com	j***@c****.com
Phone	(555) 123-4567	(***) ***-4567

Query Audit Logging

Every query is logged with:

• Timestamp and unique query fingerprint
• Row count and execution duration
• PII detection results
• Policy violation flags

Logs support compliance requirements (GDPR, HIPAA, SOC2).

Configurable Access Policies

Define granular policies in config/policies.yaml:

tables:
  - name: Customers
    columns:
      - name: SSN
        action: block      # Never return
      - name: Email
        action: mask       # Apply PII masking
      - name: Revenue
        action: allow      # Return as-is

---

Installation

Prerequisites

For Desktop Connectivity:

• Windows 10/11
• Power BI Desktop installed
• Python 3.10+
• ADOMD.NET (included with Power BI Desktop or SSMS)

For Cloud Connectivity (Optional):

• Azure AD App Registration with Dataset.Read.All and Workspace.Read.All
• Premium Per User (PPU) or Premium Capacity workspace
• XMLA endpoint enabled

Quick Start

# Clone the repository
git clone https://github.com/sulaiman013/powerbi-mcp.git
cd powerbi-mcp-v2

# Install dependencies
pip install -r requirements.txt

# (Optional) Configure cloud credentials
cp .env.example .env
# Edit .env with your Azure AD credentials

Configure Claude Desktop

Add to %APPDATA%\Claude\claude_desktop_config.json:

{
  "mcpServers": {
    "powerbi": {
      "command": "python",
      "args": ["C:/path/to/powerbi-mcp-v2/src/server.py"],
      "env": {
        "PYTHONPATH": "C:/path/to/powerbi-mcp-v2/src"
      }
    }
  }
}

Restart Claude Desktop to activate.

---

Usage Examples

⚠️ IMPORTANT: How to Rename Tables/Columns/Measures

DO NOT use TOM-based tools (batch_rename_tables, batch_rename_columns, batch_rename_measures) for renaming. They break report visuals!

ALWAYS use PBIP tools:

1. Close Power BI Desktop (if open)
2. pbip_load_project - Load the .pbip file
3. pbip_rename_tables / pbip_rename_columns / pbip_rename_measures - Do the rename
4. Reopen Power BI Desktop to see changes

Basic Desktop Workflow

User: "Discover Power BI Desktop instances"
User: "Connect to Power BI Desktop"
User: "What tables are in my model?"
User: "Run DAX: EVALUATE TOPN(10, Sales)"

Safe Bulk Rename with PBIP (✅ Correct Way)

User: "Load PBIP project from C:/Projects/SalesReport"
User: "Rename tables: Salesforce_Data to Leads Sales Data"

What happens automatically:

1. Table declaration updated: table Salesforce_Data → table 'Leads Sales Data'
2. DAX references quoted: Salesforce_Data[Amount] → 'Leads Sales Data'[Amount]
3. Function calls fixed: COUNTROWS(Salesforce_Data) → COUNTROWS('Leads Sales Data')
4. Visual files updated: All "Entity": "Salesforce_Data" → "Entity": "Leads Sales Data"
5. Cultures/Q&A updated: All "ConceptualEntity" references fixed

Diagnose Issues (When Needed)

User: "Load PBIP project from C:/Projects/SalesReport"
User: "Scan for broken references"
# Shows: Tables in model vs tables referenced in visuals

User: "Validate project"
# Shows: Any TMDL syntax errors or quoting issues

Use repair tools only if a rename was done outside the MCP server (e.g., via TOM API or Power BI Desktop UI).

RLS Testing

User: "List RLS roles in my model"
User: "Set RLS role to 'Sales_East'"
User: "Run DAX: EVALUATE Sales"  # Results filtered by role
User: "Clear RLS role"

---

Project Structure

powerbi-mcp-v2/
├── src/
│   ├── server.py                    # MCP server (30 tools)
│   ├── powerbi_desktop_connector.py # Desktop + RLS
│   ├── powerbi_xmla_connector.py    # Cloud XMLA
│   ├── powerbi_rest_connector.py    # REST API
│   ├── powerbi_tom_connector.py     # TOM write operations
│   ├── powerbi_pbip_connector.py    # PBIP file editing
│   └── security/
│       ├── pii_detector.py          # PII detection
│       ├── audit_logger.py          # Audit logging
│       ├── access_policy.py         # Policy engine
│       └── security_layer.py        # Unified security
├── config/
│   └── policies.yaml                # Access policies
├── logs/
│   └── audit.log                    # Query audit log
├── .env.example
├── requirements.txt
└── README.md

---

Limitations

Limitation	Workaround
Windows only	Required for ADOMD.NET and Power BI Desktop
TOM renames break visuals	Use PBIP tools for safe bulk renames
Cloud requires Premium	XMLA endpoints need PPU/Premium workspace

---

Roadmap

Completed (V2)

• Power BI Desktop connectivity
• Cloud XMLA integration
• Security layer (PII, audit, policies)
• RLS testing support
• TOM write operations
• PBIP safe editing

Planned (V3)

• Open Source LLM Support - Integration with Ollama, LM Studio, and other local LLMs
• Air-Gapped Deployment - Full offline capability for secure enterprise environments
• Docker Containerization - One-command deployment with docker-compose
• Self-Hosted Architecture - Run entirely on-premise without external dependencies
• Relationship management (create/delete)
• VertiPaq Analyzer integration
• Auto-generated model documentation
• Cross-platform support exploration

---

Author

Sulaiman Ahmed Data Analytics Engineer & Microsoft Certified Professional Passionate about bridging the gap between AI and enterprise data platforms. This project combines expertise in Power BI semantic modeling, Python development, and the emerging Model Context Protocol ecosystem.

---

Contributing

Contributions are welcome! Please read our contributing guidelines before submitting PRs.

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

---

License

This project is licensed under the MIT License - see the LICENSE file for details.

---

Acknowledgments

• Model Context Protocol by Anthropic
• Microsoft's TOM and TMDL documentation
• The Power BI community for insights on PBIP format

---

_{Built with passion for the Power BI and AI community}
Report Bug | Request Feature