powerbi-mcp
服务介绍
Power BI MCP Server
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 operationsall 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) formata 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:
- Parses PBIP Structure - Locates all TMDL files and
report.json - Updates Model Layer - Regex-based find-replace in TMDL files for:
- Table definitions (
table OldNametable NewName) - DAX references (
'OldTable'[Column]'NewTable'[Column])
- Table definitions (
- Updates Report Layer - JSON manipulation in
report.jsonfor:- Entity bindings (
"Entity": "OldTable""Entity": "NewTable") - Query references (
"OldTable.Column""NewTable.Column") - Native reference names
- Entity bindings (
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 |
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.AllandWorkspace.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:
- Close Power BI Desktop (if open)
pbip_load_project- Load the .pbip filepbip_rename_tables/pbip_rename_columns/pbip_rename_measures- Do the rename- 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:
- Table declaration updated:
table Salesforce_Datatable 'Leads Sales Data' - DAX references quoted:
Salesforce_Data[Amount]'Leads Sales Data'[Amount] - Function calls fixed:
COUNTROWS(Salesforce_Data)COUNTROWS('Leads Sales Data') - Visual files updated: All
"Entity": "Salesforce_Data""Entity": "Leads Sales Data" - 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.
- Fork the repository
- Create a feature branch (
git checkout -b feature/amazing-feature) - Commit your changes (
git commit -m 'Add amazing feature') - Push to the branch (
git push origin feature/amazing-feature) - 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
