Power BI MCP AI Chatbot

An AI-powered chatbot that allows users to interact with Power BI semantic models using natural language.

The project uses the Model Context Protocol (MCP) to connect an AI application with Power BI Desktop, discover semantic-model metadata, generate and validate DAX queries, execute those queries, and return meaningful results to the user.

Project Status: 🚧 In Development

Project Overview

The objective of this project is to build a generic AI chatbot for interacting with Power BI semantic models through natural language.

Instead of requiring users to manually write DAX queries, the chatbot will allow users to ask questions such as:

• What is the total revenue?

• Which category has the highest sales?

• What are the total orders?

• Show sales by region.

• What was the performance last year?

• Which product generated the most revenue?

The AI layer will understand the user's question, use information about the connected Power BI semantic model, generate an appropriate DAX query, validate the query, execute it through MCP, and explain the result in natural language.

The chatbot is designed to work with different Power BI semantic models rather than being tied to a specific dataset.

Related MCP server: PBIXRay MCP Server

Core Architecture

The planned end-to-end architecture is:

User
  |
  v
Chat UI
  |
  v
LLM
  |
  | Natural Language -> DAX
  v
MCP Client
  |
  v
Power BI Modeling MCP Server
  |
  v
Power BI Desktop
  |
  v
Power BI Semantic Model

The semantic model can represent different business domains and datasets.

For example:

Sales
Finance
HR
Operations
Supply Chain
Government Schemes
Customer Analytics
Any compatible Power BI model

The chatbot should discover the structure of the connected model dynamically rather than depending on hard-coded table or column names.

Current Working Architecture

The MCP and Power BI integration layer has already been implemented and tested successfully.

Current architecture:

Python Application
      |
      v
Power BI MCP Client
      |
      v
Microsoft Power BI Modeling MCP Server
      |
      v
Power BI Desktop
      |
      v
Power BI Semantic Model
      |
      v
DAX Validation / Execution

The LLM and Chat UI will be added on top of this foundation.

How the System Will Work

The final chatbot workflow is expected to follow this process:

1. User asks a question
          |
          v
2. LLM understands the question
          |
          v
3. Application provides relevant semantic-model context
          |
          v
4. LLM generates DAX
          |
          v
5. MCP client sends DAX to Power BI
          |
          v
6. Power BI MCP Server validates the DAX
          |
          v
7. Valid DAX is executed
          |
          v
8. Power BI returns structured results
          |
          v
9. LLM interprets the result
          |
          v
10. Chatbot presents the answer to the user

Key Design Principle

The chatbot should not be tightly coupled to one particular Power BI dataset.

Instead of writing logic such as:

if user asks about pizza:
    use pizza_category

the application will dynamically discover information from the connected semantic model.

The AI layer can therefore work with different models as long as they are available through the supported Power BI MCP integration.

Power BI Semantic Model Discovery

The MCP client can discover information from the connected Power BI semantic model.

Current capabilities include:

• Discovering local Power BI Desktop instances

• Connecting to a Power BI Desktop semantic model

• Retrieving model metadata

• Listing tables

• Retrieving table details

• Discovering columns

• Listing measures

• Retrieving measure definitions

This information will eventually become part of the context provided to the LLM.

For example:

Semantic Model
    |
    +-- Tables
    |     |
    |     +-- Columns
    |
    +-- Measures
    |
    +-- Relationships
    |
    +-- Other metadata

This allows the LLM to understand what data is actually available before generating DAX.

DAX Generation Strategy

The LLM will eventually receive relevant semantic-model information and use it to generate DAX.

Example:

User:
"What is the total revenue?"

LLM:
Generates an appropriate DAX query based on
the connected semantic model.

The generated DAX will not be blindly executed.

The intended workflow is:

Natural Language
      |
      v
    LLM
      |
      v
   DAX Query
      |
      v
DAX Validation
      |
   Valid?
    /   \
  Yes    No
   |      |
   v      v
Execute  Regenerate / Handle Error
   |
   v
 Result
   |
   v
LLM Explanation

MCP Responsibilities

The Model Context Protocol layer is responsible for providing a standardized interface between the AI application and the Power BI environment.

In this project, MCP is used for operations such as:

• Power BI Desktop discovery

• Connection management

• Semantic-model discovery

• Table discovery

• Column discovery

• Measure discovery

• Measure definition retrieval

• DAX validation

• DAX execution

The LLM itself does not directly communicate with Power BI Desktop.

The MCP client acts as the application-side interface to the Power BI MCP Server.

LLM Responsibilities

The LLM layer will be responsible for:

• Understanding natural-language questions

• Identifying the user's analytical intent

• Using semantic-model context

• Generating DAX

• Correcting invalid DAX

• Interpreting query results

• Producing natural-language explanations

• Maintaining conversational context where appropriate

The LLM should not be responsible for directly connecting to Power BI.

Current Development Test Model

During development, a local Power BI Desktop model containing a sample dataset is being used to test the MCP integration.

This dataset is only a development and validation model.

It is not part of the chatbot's core architecture.

The application is being designed so that the connected Power BI semantic model can be changed without rewriting the chatbot architecture.

Current MCP Capabilities

The reusable Python MCP client currently supports:

Power BI Discovery

• Discover local Power BI Desktop instances

• Connect to a Power BI Desktop model

Semantic Model Discovery

• Retrieve model information

• List tables

• Retrieve table details

• Retrieve columns

Measure Discovery

• List measures

• Retrieve measure definitions

DAX

• Validate DAX queries

• Execute DAX queries

• Retrieve structured DAX results

Project Structure

power-bi-mcp-ai-chatbot/
|
+-- .gitignore
+-- config.py
+-- mcp_client.py
+-- requirements.txt
+-- test_mcp_client.py
+-- README.md

Additional files and directories will be introduced as the LLM, backend, UI, testing, and documentation layers are developed.

File Responsibilities

config.py

Contains configuration related to the Power BI MCP Server.

The MCP server command and arguments are separated from the application logic so that configuration can be maintained independently.

mcp_client.py

Contains the reusable PowerBIMCPClient class.

This class provides application-level methods for communicating with the Power BI MCP Server.

test_mcp_client.py

Contains the current integration test and demonstration workflow.

It verifies that the MCP client can:

• Discover Power BI Desktop

• Connect to the semantic model

• Retrieve model metadata

• Discover tables

• Discover columns

• Discover measures

• Retrieve measure definitions

• Validate DAX

• Execute DAX

requirements.txt

Contains the Python dependencies required by the project.

.gitignore

Prevents local development files, virtual environments, cache files, logs, and environment variables from being committed to Git.

README.md

Contains project documentation, architecture, setup information, development progress, and future implementation details.

Technologies

Current technologies:

• Python

• Microsoft Power BI Desktop

• Microsoft Power BI Modeling MCP Server

• Model Context Protocol

• DAX

• Git

• GitHub

Planned technologies:

• LLM / NLP API

• Backend web framework

• Web-based Chat UI

• Environment-variable based configuration

• Automated testing

The final technology stack will be documented as the implementation progresses.

Development Environment

Current development environment:

Operating System : Windows
Python           : 3.13.3
Node.js          : 22.15.0
npm              : 10.9.2
Power BI Desktop : 2.157.1354.0
MCP SDK          : 2.2.0
Black            : 26.5.1

Security Considerations

Security will be considered throughout the project.

Planned practices include:

• API keys stored in environment variables

• .env excluded from Git

• No API keys committed to GitHub

• Input validation

• DAX validation before execution

• Controlled MCP tool usage

• Error handling

• Avoiding unnecessary exposure of sensitive model information

• Separating configuration from application logic

Secrets will be introduced only when the LLM integration is implemented.

Development Roadmap

Phase 1 — Project and MCP Foundation

• Create project structure

• Create Python virtual environment

• Install MCP SDK

• Configure Power BI Modeling MCP Server

• Discover Power BI Desktop

• Connect to Power BI Desktop

• Discover semantic model

• Discover tables

• Discover columns

• Discover measures

• Retrieve measure definitions

• Validate DAX

• Execute DAX

• Create reusable MCP client

• Separate testing from reusable client code

• Add requirements.txt

• Add .gitignore

• Initialize Git repository

• Create initial Git commit

• Connect project to GitHub

• Push initial implementation

Phase 2 — LLM Integration

• Evaluate free / low-cost LLM APIs

• Select an LLM suitable for DAX generation

• Install LLM dependencies

• Create environment configuration

• Create .env

• Protect API credentials

• Implement LLM client

• Design system prompt

• Design DAX-generation prompt

• Test natural-language-to-DAX generation

Phase 3 — Semantic Model Context

• Build structured semantic-model metadata

• Extract tables dynamically

• Extract columns dynamically

• Extract measures dynamically

• Retrieve measure definitions when required

• Provide relevant schema context to the LLM

• Avoid hard-coded dataset-specific assumptions

• Design context-selection strategy

Phase 4 — AI + MCP Integration

• User question processing

• Natural language → DAX

• DAX validation through MCP

• DAX execution through MCP

• Result parsing

• Error handling

• Automatic DAX correction

• Result interpretation

• Natural-language response generation

• Conversational context

Phase 5 — Chatbot Backend

• Create backend application

• Create chat endpoint

• Connect backend to LLM

• Connect backend to MCP client

• Implement request/response handling

• Implement error handling

• Implement logging

• Add configuration management

Phase 6 — Chatbot UI

• Design professional chat interface

• User message interface

• AI response interface

• Loading indicators

• Error messages

• DAX display

• Query-result display

• Table-result formatting

• Conversation history

• Responsive design

Phase 7 — Testing

• Unit tests

• MCP integration tests

• Semantic-model discovery tests

• DAX validation tests

• DAX execution tests

• LLM generation tests

• Error scenarios

• Invalid-question scenarios

• Invalid-DAX scenarios

• Security tests

• End-to-end testing

Phase 8 — Documentation

• Architecture documentation

• Installation guide

• Configuration guide

• LLM configuration guide

• MCP setup guide

• Power BI setup guide

• Troubleshooting guide

• Development notes

• Screenshots

• Example conversations

• Example DAX generation

• Final project documentation

Phase 9 — Portfolio Finalization

• Clean project structure

• Final code review

• Security review

• Performance review

• Git history cleanup if required

• Final README

• Architecture diagram

• Demo

• Portfolio presentation

Current Project Status

The Power BI MCP integration layer is working successfully.

The application can currently:

Discover Power BI Desktop
      |
      v
Connect to the semantic model
      |
      v
Discover model metadata
      |
      v
Discover tables and columns
      |
      v
Discover measures
      |
      v
Retrieve measure definitions
      |
      v
Validate DAX
      |
      v
Execute DAX
      |
      v
Receive structured results

The next major stage is the LLM integration layer.

Important Architectural Goal

The final chatbot should be model-agnostic at the application level.

The chatbot should not assume:

• Specific table names

• Specific column names

• Specific measures

• Specific business domains

• Specific datasets

Instead, it should use the metadata of the currently connected Power BI semantic model.

This allows the same chatbot architecture to be used with different Power BI models.

Project Philosophy

This project is being developed as both:

1. A functional AI application

2. A learning and portfolio project

Development will therefore focus on understanding:

• MCP

• Power BI semantic models

• DAX

• LLMs

• Natural-language-to-DAX generation

• AI application architecture

• API integration

• Prompt engineering

• Backend development

• Frontend development

• Security

• Testing

• Git/GitHub workflows

• Production-oriented design

Each major implementation stage will be tested before moving to the next stage.

License

A license will be selected and added when the project implementation is finalized.