sulaiman013/powerbi-mcp

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

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:

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

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)

Cloud Operations (6 tools)

Security & Compliance (2 tools)

Row-Level Security (3 tools)

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.

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.

PBIP Diagnostics (4 tools)

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:

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

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

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 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