Excel Power Pivot Mcp

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