# 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](https://github.com/back1ply/Excel-Power-Pivot-MCP/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`

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

### Cursor

Add to your Cursor MCP settings:

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

### Antigravity (VS Code)

Add to your `.vscode/mcp.json`:

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

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

```text
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):

```json
{
  "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](LICENSE) file.

## Contributing

Contributions welcome! Please open an issue or pull request.

## Acknowledgments

- [Model Context Protocol](https://modelcontextprotocol.io/) by Anthropic
- [DAX Formatter](https://www.daxformatter.com/) by SQLBI