Content
# Power BI Desktop Local MCP Server
A Model Context Protocol (MCP) server that enables AI assistants (such as Claude Desktop, Cursor, Cline, and others) to securely and dynamically connect to locally running **Power BI Desktop** instances in Windows.
Through this server, an LLM can discover open reports, inspect table and column schema (metadata), and execute custom DAX queries to interact with your data or generate visualizations dynamically.
---
## Key Features
- **Dynamic Port Discovery:** Power BI Desktop spins up a local instance of SQL Server Analysis Services (SSAS) and assigns it a random port every session. This server automatically scans Windows `AppData` directories to identify active workspace sessions and resolve their local ports.
- **Robust Connection via ADOMD.NET:** Instead of relying on local system OLE DB providers (`MSOLAP`) which often suffer from architecture mismatches (32-bit vs. 64-bit) or missing drivers, this server leverages `pythonnet` to directly load the native `Microsoft.PowerBI.AdomdClient.dll` shipped with Power BI Desktop.
- **Schema Inspection:** Exposes database schema, detailing tables, columns, data types, and visibility states.
- **JSON-Safe DAX Execution:** Executes complex DAX expressions (e.g., `EVALUATE SUMMARIZECOLUMNS(...)`) and parses raw .NET data types (decimals, dates, nulls) into clean, JSON-serializable structures.
- **Self-Sanitizing Environment:** Automatically isolates its environment from host platforms (such as AI agent runners or global shells) by clearing contaminated `PYTHONPATH`/`PYTHONHOME` environment variables and prioritizing the local `.venv` directory to prevent runtime dependency import issues (e.g., `pywintypes` or `pythonnet` collision).
---
## Project Structure
- `pbi_connector.py`: Core database connector utilizing ADOMD.NET client libraries and local active port scanning.
- `server.py`: Entry point for the MCP server built with the high-level `FastMCP` framework.
- `requirements.txt`: Python package dependencies.
- `create_dashboard.py`: A companion script demonstrating how to run DAX queries, aggregate data in `pandas`, and generate a premium web-based dashboard with `Plotly`.
---
## Prerequisites
1. **Operating System:** Windows (required to run Power BI Desktop and load the native Windows .NET Assemblies).
2. **Python:** Version 3.10 or higher.
3. **Power BI Desktop:** Installed standard edition (`C:\Program Files\Microsoft Power BI Desktop`) or Microsoft Store edition.
---
## Installation & Setup
### 1. Environment Setup
Navigate to the project directory, initialize a virtual environment, and install dependencies:
```powershell
# Create virtual environment
python -m venv .venv
# Activate virtual environment
.\.venv\Scripts\Activate.ps1
# Install requirements
pip install -r requirements.txt
```
### 2. Standalone Execution
With an active Power BI Desktop file open, you can test the MCP server locally in your terminal:
```powershell
fastmcp run server.py
```
---
## MCP Client Registration
To use this server inside your AI editor or chat client (e.g., Cursor, Cline, or Claude Desktop), register the server in your MCP configuration file.
### Configuration Configuration (`mcp_config.json` or `claude_desktop_config.json`)
Append the following block to your `mcpServers` object (ensure you replace paths with the absolute path of your workspace):
```json
{
"mcpServers": {
"powerbi-local": {
"command": "C:\\Path\\To\\Your\\powerbi-mcp\\.venv\\Scripts\\python.exe",
"args": [
"C:\\Path\\To\\Your\\powerbi-mcp\\server.py"
]
}
}
}
```
---
## MCP Tools Reference
The server exposes the following tools:
1. **`list_instances`**
- *Description:* Detects and returns active local Power BI Desktop instances on the current machine.
- *Returns:* List of objects containing the active local port and temporary directory path.
2. **`get_schema`**
- *Parameters:* `port` (string)
- *Description:* Connects to the local port and returns the semantic model table/column structure.
3. **`execute_dax`**
- *Parameters:* `port` (string), `query` (string)
- *Description:* Executes a custom DAX query (e.g., `EVALUATE 'Table'`) and returns rows formatted as JSON.
4. **`add_measure_to_tmdl`**
- *Parameters:* `tmdl_path` (string), `name` (string), `expression` (string), `format_string` (string, optional)
- *Description:* Appends a DAX measure directly to a local table's `.tmdl` file on disk. This guarantees the measure is permanently saved in the Power BI Project (PBIP) metadata instead of only living in the temporary active memory session. It automatically handles double-quoting the format string to prevent syntax errors.
---
## Best Practices & Troubleshooting (PBIP / TMDL / PBIR)
During manual or agent-based programmatic editing of Power BI Projects, follow these strict development rules to avoid report corruption and application load crashes:
### 1. TMDL formatString Quoting Rules
In Tabular Model Definition Language (TMDL) files, any `formatString` property containing special characters, symbols, operators, or spacing (such as `$`, `%`, `;`, `+`, or spaces) **must be enclosed in double quotes**.
* **Incorrect:** `formatString: $#,##0` (causes Power BI Desktop crash on load)
* **Correct:** `formatString: "$#,##0"` or `formatString: "+0.0%;-0.0%"`
* *Note:* The `add_measure_to_tmdl` tool handles this automatically.
### 2. Guarding Against Duplicate Measures
Before appending DAX measures to a `.tmdl` file, verify if the measure is already defined (e.g. at the beginning of the file from a prior `.pbix` export). Defining a measure twice will trigger a fatal TMDL compilation error: `"No se pueden combinar objetos TMDL porque ambos declaran la misma propiedad: expression"`.
### 3. Git Integration and Root Path Permission Error
Power BI Desktop automatically detects Git repositories up the directory tree to configure its project settings. If a `.git` folder accidentally exists in the root drive (e.g., `C:\.git`), Power BI will attempt to write a `.gitignore` file at `C:\.gitignore`. Because non-admin users cannot write to the root of `C:\`, saving the project will crash with: `Acceso denegado a la ruta de acceso 'C:\.gitignore'`.
* **Fix:** Delete the accidental root-level `C:\.git` folder.
### 4. Valid PBIR visualType Identifiers
When injecting custom visual layouts inside `.Report\definition\pages\[page]\visuals\[visual]\visual.json`, only use official Power BI built-in `visualType` names. Using invalid names causes the entire report to fail to load on startup:
* **Invalid:** `columnClusteredChart`, `columnStackedChart`
* **Valid standard chart types:**
- `"columnChart"` (handles both clustered column and stacked column layouts)
- `"barChart"` (handles both clustered bar and stacked bar layouts)
- `"lineChart"`, `"pieChart"`, `"donutChart"`, `"treemap"`, `"card"`, `"matrix"`, `"table"`, `"waterfallChart"`, `"areaChart"`, `"scatterChart"`
