Excel Power Pivot MCP Server

A Model Context Protocol (MCP) server that enables AI assistants to interact with Excel Power Pivot data models. Create and manage DAX measures, relationships, and more through natural language.

Features

- Workbook Discovery & Connection - Discover and connect to open Excel workbooks with Power Pivot data models
- DAX Query Execution - Run DAX queries and preview table data
- Measure Management - Create, update, and delete DAX measures with auto-formatting
- Relationship Management - Create, delete, and activate/deactivate table relationships
- Model Exploration - List tables, columns, measures, relationships, hierarchies, KPIs, and dependencies
- Data Profiling - Analyze column statistics (min, max, distinct count, nulls, sample values)
- Power Query Discovery - List Power Queries (M code) in the workbook
- Table Management - Add Excel tables to the data model, refresh tables/model

> [!WARNING]
> Use at your own risk. This tool modifies your Excel Power Pivot data models directly. The author is not responsible for any data loss, corruption, or damage to your workbooks. Always maintain backups of your Excel files before using this tool.

Requirements

- Windows 10/11 (required for Excel COM interop)
- Microsoft Excel 2013+ with Power Pivot enabled

Installation

Download the latest ExcelPowerPivotMcp.exe from the Releases page.

No installation required - just download and configure your MCP client.

MCP Client Configuration

Claude Desktop

Add to your claude_desktop_config.json:

Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "excel-powerpivot": {
      "command": "C:/path/to/ExcelPowerPivotMcp.exe"
    }
  }
}

Cursor

Add to your Cursor MCP settings:

{
  "mcpServers": {
    "excel-powerpivot": {
      "command": "C:/path/to/ExcelPowerPivotMcp.exe"
    }
  }
}

Antigravity (VS Code)

Add to your .vscode/mcp.json:

{
  "servers": {
    "excel-powerpivot": {
      "type": "stdio",
      "command": "C:/path/to/ExcelPowerPivotMcp.exe"
    }
  }
}

Usage

1. Open Excel with Power Pivot

Open your Excel workbook that contains a Power Pivot data model.

2. Connect via MCP

The AI assistant will first discover and connect to your workbook:

AI: Let me discover your Excel workbooks...
→ discover_workbooks()
→ Found: MyModel.xlsx (hasDataModel: true)
AI: Connecting to your workbook...

→ connect_workbook(workbook_name: "MyModel.xlsx")

→ Connected!

3. Explore and Modify

AI: Let me see what's in your data model...
→ get_model_summary()
→ 5 tables, 12 measures, 6 relationships
AI: I'll create a new measure for you...

→ create_measure(

    tableName: "Sales",

    measureName: "Total Revenue",

    expression: "SUM(Sales[Amount])"

  )
AI: Don't forget to save!

→ save_workbook()

Available Tools

Connection

| Tool | Description |
| ----------------------- | ------------------------------------------------- |
| discover_workbooks | Find open Excel workbooks with Power Pivot models |
| connect_workbook | Connect to a specific workbook |
| get_connection_status | Check current connection status |
| save_workbook | Save the connected workbook |
| refresh_model | Refresh the entire data model |

Model Metadata

| Tool | Description |
| -------------------- | ------------------------------- |
| get_model_summary | Comprehensive model overview |
| list_tables | List all tables with row counts |
| list_columns | List columns in a table |
| list_measures | List measures with expressions |
| list_relationships | Show table relationships |
| list_hierarchies | List user-defined hierarchies |
| list_kpis | List Key Performance Indicators |
| get_dependencies | Show calculation dependencies |
| list_power_queries | List Power Queries (M code) |
| list_excel_tables | List Excel tables (ListObjects) |

DAX Queries

| Tool | Description |
| ---------------- | ----------------------------------------- |
| run_dax | Execute DAX queries or preview table data |
| analyze_column | Get column statistics and sample values |

Measure CRUD

| Tool | Description |
| ---------------- | ---------------------------------------------------------------- |
| create_measure | Create a new DAX measure (supports autoFormat=false for speed) |
| update_measure | Update expression/name/description |
| delete_measure | Delete a measure |

Relationship CRUD

| Tool | Description |
| ------------------------- | ---------------------------------- |
| create_relationship | Create a table relationship |
| delete_relationship | Delete a relationship |
| set_relationship_active | Activate/deactivate a relationship |

Table Operations

| Tool | Description |
| -------------------- | ------------------------------------------- |
| add_table_to_model | [DESTRUCTIVE] Add Excel table to data model |
| delete_table | [DESTRUCTIVE] Delete a table from the model |
| refresh_table | Refresh a single table |

Prompts

| Prompt | Description |
| --------------------------- | ------------------------------------------------ |
| describe_model | Describe the data model in plain English |
| workflow_create_measure | Guided workflow to create a measure |
| workflow_analyze_model | Analyze model structure and suggest improvements |
| workflow_test_dax | Test DAX before creating permanent measures |
| workflow_fix_measure | Debug and fix a broken measure |
| workflow_add_relationship | Guided workflow to add a relationship |
| recover_connection | Recover from a lost Excel connection |

Resources

| URI | Description |
| ------------------------ | -------------------------------------------- |
| model://schema | Current Power Pivot data model schema (JSON) |
| model://dirty-state | Check for unsaved changes |
| model://logs | Recent server diagnostic logs |
| model://instructions | Guidelines for working with Power Pivot |
| model://best-practices | Measure best practices guide |
| model://dax-guide | DAX query guide for Excel |
| model://workflows | Common workflow reference |

Performance Tips

Fast Measure Creation

Use autoFormat: false to skip DAX formatting for faster measure creation (~1.5s savings):

{
  "tableName": "Sales",
  "measureName": "Quick Measure",
  "expression": "SUM(Sales[Amount])",
  "autoFormat": false
}

Limitations

Excel Power Pivot Limitations

These features are not available in Excel Power Pivot (unlike Power BI):

| Feature | Excel Power Pivot |
| ---------------------------- | ------------------- |
| Calculation Groups | ❌ Not supported |
| Perspectives | ❌ Not supported |
| Row-Level Security (RLS) | ❌ Not supported |
| DEFINE COLUMN in DAX queries | ❌ Not supported |

MCP Server Limitations

These exist in Excel but cannot be managed via this MCP due to COM API restrictions:

| Feature | Status |
| --------------------------------------- | ------------------------- |
| Create/Update/Delete Calculated Columns | ❌ Use Power Pivot window |
| Set Column Descriptions | ❌ Use Power Pivot window |

Troubleshooting

| Error | Solution |
| ---------------------- | ------------------------------------- |
| "Excel is not running" | Open Excel with your workbook |
| "Workbook not found" | Ensure the workbook is open in Excel |
| "No data model" | Create a Power Pivot data model first |
| "Not connected" | Call connect_workbook first |

License

MIT License - see LICENSE file.

Contributing

Contributions welcome! Please open an issue or pull request.

Acknowledgments

- Model Context Protocol by Anthropic
- DAX Formatter by SQLBI