Agentic AI with Power BI MCP and Claude

Devin Knight returns with part two of the video series on PowerBI Modeling and MCP Server. This tutorial dives into leveraging Claude, a tool that helps automate and streamline the process of modifying and improving PowerBI data models through simple prompts.

Getting Started: Connecting Claude to PowerBI

The first step is connecting Claude Desktop to a PowerBI Desktop file. In the video, Devin demonstrates how to establish a connection to the file and start making improvements to the model using prompts.

• Connect to the PowerBI Desktop file using Claude Desktop.
• Verify the connection by listing available tables in the model.

Renaming Tables and Columns with Prompts

One of the first actions is to rename tables and columns for better clarity and consistency. Devin uses Claude to automate the renaming process, ensuring the tables follow best practices, such as removing unnecessary prefixes like "Fact" and "Dim" from table names.

• Use prompts to rename columns with spaces between words for easier readability.
• Remove "Fact" or "Dim" from table names to make them more user-friendly.
• Track all changes made by Claude, including a detailed report on the renamed tables and columns.

Hiding Key Columns

Key columns, such as primary keys, are often necessary for relationships but can clutter the PowerBI model for end users. Devin shows how Claude can hide these key columns to make the model cleaner for report creators.

• Run a prompt to hide all key columns from the model view.
• Reduce clutter and improve user experience by focusing on important columns for reporting.

Creating Hierarchies Automatically

Next, Devin shows how Claude can automatically create hierarchies based on the data structure of the model. Claude analyzes the model and determines where hierarchies are needed, such as in the "Sales Territory" and "Product" tables.

• Automatically generate hierarchies for tables like "Sales Territory" and "Product".
• Review the created hierarchies to ensure they align with the business logic.

Building Relationships Between Tables

Devin demonstrates the power of Claude in establishing relationships between tables. He uses a missing relationship between the "Date" table and the "Internet Sales" table as an example, allowing Claude to choose the appropriate key (order date) for the relationship.

• Use prompts to create relationships between tables using appropriate columns.
• Claude selects the best columns for establishing relationships, such as using the "Order Date" key for sales analysis.

Creating a Composite Key for a New Relationship

Devin explains how Claude can help generate a composite key when there’s no direct relationship between two tables. In this case, he combines the "Region" column from the "Sales Territory" table with the "Month Number" from the "Date" table to create a relationship between "Regional Temperature" and "Internet Sales".

• Combine columns from multiple tables to create a new composite key.
• Use Claude’s DAX to generate the necessary relationships.

Generating a Complete Markdown Document for Model Documentation

One of the standout features of using Claude is the ability to generate a detailed documentation of the entire PowerBI model. Devin shows how to create a markdown file that documents the entire model, including table relationships, measures, and data sources.

• Create a professional documentation for the PowerBI model.
• Use mermaid diagrams to illustrate table relationships.
• Generate a comprehensive markdown file with DAX code, relationships, and descriptions for each measure.

Conclusion

By leveraging Claude with PowerBI, developers can automate several time-consuming tasks, such as renaming tables, creating relationships, and documenting the model. These features make it easier to maintain clean, well-structured models and improve productivity.

Check out the full video on the Pragmatic Works channel to see the steps in action.


 Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.