Using ChatGPT in Power BI: Building Smarter Reports with Generative AI

Introduction: AI Meets BI

Generative AI has revolutionised data work. ChatGPT combined with Power BI enables reports that not only display data but also understand and explain it in natural language. This integration allows analysts to ask dashboards questions and receive AI-generated insights automatically, without heavy coding requirements.

Why Integrate ChatGPT with Power BI?

Power BI’s native AI features have limitations. ChatGPT acts as a translator between human queries and analytics, enabling:

• Auto-generating insights: Create commentary and conclusions for charts
• Natural language querying: Convert questions into DAX or SQL expressions
• Text analysis: Summarise survey responses and categorise data
• Data preparation tips: Receive guidance on cleaning and structuring data
• Learning support: New analysts can ask ChatGPT to explain confusing code

These capabilities translate to faster report building. One example involved categorising song emotions using ChatGPT integrated into Power Query — the AI labelled entries as “Romance” or “Freedom” automatically.

Power BI’s Built-in AI vs. ChatGPT Integration

Microsoft’s Copilot for Power BI handles basic tasks like answering simple data questions. However, custom ChatGPT integration via OpenAI’s API offers “much greater flexibility” for complex workflows, external API calls, and customised prompts with business context.

The choice depends on needs: quick out-of-box help suits Copilot; tailored, integrated solutions require custom ChatGPT integration.

Integrating ChatGPT in Power BI

1. Using Power Query (M) to Call ChatGPT

This approach embeds ChatGPT directly into data transformations:

1. Get an OpenAI API Key from platform.openai.com
2. Open Power Query’s Advanced Editor in Power BI Desktop
3. Insert the ChatGPT API call using M code:

let
    apiKey = "YOUR_API_KEY",
    apiUrl = "https://api.openai.com/v1/chat/completions",
    headers = [
        #"Content-Type" = "application/json",
        #"Authorization" = "Bearer " & apiKey
    ],
    requestBody = Json.FromValue([
        model = "gpt-4",
        messages = {
            [ role = "system", content = "You are a helpful assistant." ],
            [ role = "user", content = "What is the capital of France?" ]
        },
        max_tokens = 50
    ]),
    response = Web.Contents(apiUrl, [Headers=headers, Content=requestBody]),
    responseJson = Json.Document(response),
    answer = responseJson[choices]{0}[message][content]
in
    answer

4. Refactor into a reusable function accepting any prompt text
5. Invoke the function on your data using Add Column → Invoke Custom Function
6. Visualise the AI output in charts and reports

This method works well for sentiment analysis, auto-tagging, generating summaries, and enriching data quality.

2. Using Python Scripts in Power BI

An alternative path uses Python scripting:

1. Enable Python in Power BI Desktop settings
2. Install required packages: pip install openai pandas
3. Use “Run Python script” in Power Query

import openai
import pandas as pd

openai.api_key = "YOUR_API_KEY"
for idx, row in dataset.iterrows():
    prompt = f"Summarise {row['Company']} performance: {row['Metric1']}, {row['Metric2']}."
    response = openai.ChatCompletion.create(
        model="gpt-4",
        messages=[{"role": "user", "content": prompt}]
    )
    dataset.at[idx, "Insight"] = response.choices[0].message.content

This iterates over table rows, sends tailored prompts to ChatGPT, and writes responses into a new column.

Important: Python scripts run locally. Publishing to Power BI Service requires an on-premises gateway with a compatible Python environment.

3. Using Power Automate Flows with ChatGPT

Power Automate provides another integration path:

• Create a flow triggered by Power BI actions (button clicks, alerts)
• Use HTTP actions to call the OpenAI API
• Return results to Power BI via datasets or notifications

This creates basic chatbot interfaces, allowing users to click a button and receive AI-generated explanations on demand.

4. Chatbots and Custom Visuals in Power BI

Microsoft Fabric and Power BI AppSource include LLM Chatbot custom visuals. These provide built-in chat boxes connecting to language model endpoints, letting users ask questions directly on dashboards.

Real-World Use Cases

Narrative Summaries: ChatGPT writes executive summaries of findings automatically (“Sales increased 15% year-over-year, with the Northeast region leading growth”).

Data Labelling: Categorise customer comments or transactions without manual rules. ChatGPT assigns emotion categories or topic tags automatically.

DAX and Query Generation: Convert natural-language requests like “Calculate average sales last quarter” into ready-made DAX code, reducing learning curves.

On-Demand Recommendations: An interface suggests next visualisations or highlights unusual data points.

Best Practices, Cost, and Security

Data Privacy: Sending data to ChatGPT means it passes through OpenAI’s servers. Avoid highly sensitive or personally identifiable information. Anonymise or aggregate data when needed.

Performance and Limits: OpenAI’s API has rate limits and latency. Complex reports calling GPT for many rows may be slow. Test on small scales before scaling broadly.

Cost Management: OpenAI charges per token. Strategies include using smaller models for non-critical tasks, caching responses, or enabling AI only on key data slices.

Incremental Implementation: Start with small pilots on one report before enterprise rollout. Test prompts and review results for accuracy before wide integration.

Conclusion: Empowering Analysts with AI

Integrating ChatGPT into Power BI makes reports smarter and more interactive. Early-career analysts can harness generative AI for writing DAX, summarising data, and answering business questions using natural language. As AI becomes embedded in Microsoft’s tools, early adopters will lead the way.

Start small, experiment often, and AI integration may become a key part of your reporting toolkit.