Power BI Desktop Local MCP Server

A Model Context Protocol (MCP) server that enables AI assistants (such as Claude Desktop, Cursor, Cline, Hermes, 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.

---

INSTALL THE MCP FOR YOUR AI AGENT WITH THIS PROMPT

Copy and paste this prompt when starting a new session with Cursor, Cline, Claude Desktop, or Hermes to have the agent configure the environment and understand the workflow automatically:

1. **Dependency Installation:** If the `.venv/` directory does not exist, initialize a Python virtual environment (`python -m venv .venv`), activate it, and install all dependencies declared in `requirements.txt`.
2. **Skill Registration:** Copy the `.agents/skills/powerbi-pbir-editor` customizations directory to your local global skills directory (e.g., in Hermes: `C:\Users\{User}\AppData\Local\hermes\skills\`).
3. **MCP Server Registration:** Register the `powerbi-local` MCP server in your global configuration file (e.g., in Hermes: `config.yaml`, in Cursor/Cline: `mcpjson.json`).
   - **CRITICAL:** Configure the command pointing to the Python executable of the local virtual environment (`.venv/Scripts/python.exe`) and the arguments pointing to the `launch.py` script of this repository. This prevents sys.path conflicts (PYTHONPATH pollution) when importing the `mcp` library.
4. **Understanding Your Capabilities (CRITICAL):** Do not assume you cannot create report pages or visuals programmatically just because the MCP tools don't have visual creation functions. You have full filesystem write access. You can and should create new pages and visuals by creating directories in the `.Report/definition/pages/` folder and writing `visual.json` files in their `visuals/` subfolders according to the `powerbi-pbir-editor` skill rules. Never tell the user you cannot create visuals; you can always write files.
5. **Next Steps (Request from Human):** Once the installation is complete, present the following next steps to the human to proceed:
   - "I have successfully installed and configured the Power BI MCP server and skills."
   - "Please open Power BI Desktop with your project."
   - "Provide me with the absolute path to your `.pbip` file and the name of the table you want to work with so I can start modeling and generating charts."

---

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.
• launch.py: Sanitizing wrapper to run the server without PYTHONPATH collision.

---

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

1. Clone & Set Up Workspace

Navigate to the directory and run:

# Create virtual environment
python -m venv .venv

# Activate virtual environment
.\.venv\Scripts\Activate.ps1

# Install requirements
pip install -r requirements.txt

2. Standalone Test

With Power BI Desktop open containing a loaded dataset:

fastmcp run server.py

3. Register MCP Server in client (Cursor, Cline, Claude Desktop, Hermes)

Add the following block to your MCP config file (e.g. C:\Users\{User}\AppData\Local\hermes\config.yaml or C:\Users\{User}\AppData\Roaming\Cursor\User\globalStorage\moe.etherelf.container\mcpjson.json):

# yaml format (Hermes config.yaml)
mcp_servers:
  powerbi-local:
    command: C:/Users/{User}/powerbi-mcp/.venv/Scripts/python.exe
    args:
      - C:/Users/{User}/powerbi-mcp/launch.py
    connect_timeout: 30
    timeout: 120

// json format (Cursor / Cline config)
{
  "mcpServers": {
    "powerbi-local": {
      "command": "C:\\Users\\{User}\\powerbi-mcp\\.venv\\Scripts\\python.exe",
      "args": [
        "C:\\Users\\{User}\\powerbi-mcp\\launch.py"
      ]
    }
  }
}

---

⚠️ GOLDEN RULES FOR HUMAN AND AI AGENTS (PBIR 2.0.0+ / TMDL)

When creating or modifying Power BI report pages or semantic models programmatically, you must follow these rules strictly. Failure to do so will result in empty visual placeholders, evaluation loops, or report loading crashes.

1. Folder Structure for Visuals (PBIR 2.0.0+)

In modern Power BI projects, visual files must not be placed directly in the page folder. They must be organized inside a visuals/ subfolder, where each visual is its own directory containing a visual.json file:

{project}.Report/
  definition/
    pages/
      {page-guid}/
        page.json
        visuals/
          {visual-name}/
            visual.json

2. Visual JSON Structure (visualContainer Schema)

Projections and fields must be structured directly under visual.query.queryState (never on the root of visual.json or directly under visual).

Example visual configuration (visual.json):

{
  "$schema": "https://developer.microsoft.com/json-schemas/fabric/item/report/definition/visualContainer/2.5.0/schema.json",
  "name": "treemap-service",
  "position": { "x": 20, "y": 20, "z": 0, "width": 610, "height": 310, "tabOrder": 0 },
  "visual": {
    "visualType": "treemap",
    "query": {
      "queryState": {
        "Group": {
          "projections": [
            {
              "field": {
                "Column": {
                  "Expression": { "SourceRef": { "Entity": "comidasrapidas" } },
                  "Property": "nse"
                }
              },
              "queryRef": "comidasrapidas.nse",
              "nativeQueryRef": "nse"
            }
          ]
        },
        "Values": {
          "projections": [
            {
              "field": {
                "Measure": {
                  "Expression": { "SourceRef": { "Entity": "comidasrapidas" } },
                  "Property": "Promedio Servicio"
                }
              },
              "queryRef": "comidasrapidas.Promedio Servicio",
              "nativeQueryRef": "Promedio Servicio"
            }
          ]
        }
      }
    },
    "drillFilterOtherVisuals": true,
    "objects": {},
    "visualContainerObjects": {
      "title": [
        {
          "properties": {
            "show": { "expr": { "Literal": { "Value": "true" } } },
            "text": { "expr": { "Literal": { "Value": "'Service by NSE'" } } }
          }
        }
      ]
    }
  }
}

3. Column vs Measure Rule (CRITICAL)

• Bar/Column/Line/Combo/Funnel/Pie/Donut Charts and Treemaps DO NOT accept direct columns ("Column") on their Y-axis/Values axis. Doing so will result in an empty visual showing the "Select or drag fields" warning.
• Fix: You must first define a DAX measure in the table's .tmdl file (using the MCP tool add_measure_to_tmdl), and reference it as a "Measure" projection in the visual JSON.
• Table Visuals (tableEx): Direct column references are allowed in table visual projections ("Values" channel).

4. Projection Keys by Chart Type

• Bar, Column, Line, Combo, Funnel, Pie, Donut: Use "Category" (grouping) and "Y" (values/measure).
• Treemaps: Use "Group" (grouping) and "Values" (measure/size).
• Tables (tableEx): Use "Values" (array of column projections).

5. TMDL Model Rules (CRITICAL FOR CALCULATION GROUPS)

• discourageImplicitMeasures Setting: If you create any Calculation Group (calculationGroup) in your model, you MUST add discourageImplicitMeasures: true under the model Model block in model.tmdl. If you omit this, Power BI Desktop will fail to load with a Frown crash indicating that implicit measures must be discouraged.
• Avoid isKey Property on Dimensions: Never add isKey: true to primary key columns in standard import dimensions (like date or category dimensions). Setting isKey: true in TMDL can trigger a "cyclic reference was found during evaluation" error in Power Query. Keep the column definition simple and let the model-level relationships handle cardinality.
• Double-Quoting Format Strings: If formatString contains spaces, currencies, or symbols, always enclose it in double quotes: formatString: "$#,##0" (Correct). Unquoted strings with symbols will crash Power BI.
• Prevent duplicates: Scan the .tmdl file before inserting any measure to avoid compiling duplicate names.

6. Workflow & Cache Rules (CRITICAL)

• Close Power BI Before Edits: You MUST run taskkill /IM PBIDesktop.exe /F BEFORE making any changes to .Report or .SemanticModel files (TMDL/PBIR). If Power BI Desktop is open, it holds the files in memory and will overwrite any local disk changes upon close or save.
• Clear Schema Cache: If you perform a major schema refactoring (e.g. splitting a flat table into a Star Schema), you MUST delete the local database cache file .SemanticModel/.pbi/cache.abf. If you leave it, Power BI Desktop will load conflicting cached metadata and trigger cyclic evaluation or dependency loop errors.
• Root Git Repositories: Ensure no .git folder exists in C:\ or D:\ roots. This causes Power BI's automatic Git integration to try to write a .gitignore to the root drive, triggering an access permission crash.
• Valid JSON Keys: In visual.json, never write "size", "config", or "filters" on the root object. All dimensions must reside strictly inside "position".
• Mandatory Keys: Every field projection must contain "queryRef" and "nativeQueryRef".
• Page Folder Names: Page folders under pages/ must be named using 20-character lowercase hexadecimal strings or GUIDs. Do not use descriptive folder names.