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

Data AnalysisBusiness Intelligence
Written By Softcraft Studio .

Introduction: AI Meets BI

Generative AI has revolutionized how we work with data. ChatGPT (an advanced language model by OpenAI) can generate human-like text, code, and insights on demand. By combining ChatGPT with Power BI (Microsoft’s popular data visualization tool), analysts can create reports that not only display data but also understand and explain it in natural language. This integration opens new possibilities: imagine asking your dashboard why sales dipped last quarter or having AI auto-generate insights for your KPI charts. In fact, experts note that large language models (LLMs) like GPT enable “automation, interpretation, explanation, and generation of analytics in natural language” indatalabs.com. For data teams, that means simpler DAX formulas, automated summaries, and richer user experiences – all without heavy coding.

Why Integrate ChatGPT with Power BI?

Power BI already offers some AI features (like Q&A and AI visuals), but ChatGPT takes BI to the next level. It can act as a translator between human queries and data analytics. For instance, instead of hand-writing complex DAX, you can ask ChatGPT to “calculate the average sales in 2024” and get the correct expression back indatalabs.com. It can also generate narrative descriptions for dashboards (“Our sales grew in Q4 due to holiday promotions”), or recommend visualizations based on data trends indatalabs.com. By bridging natural language and data, ChatGPT makes analytics accessible to business users without deep technical skills. A leading AI consulting firm explains that ChatGPT integration allows building “AI-powered BI dashboards” and even complete reports from plain-English requests indatalabs.com.

Common use cases include:

  • Auto-generating insights: Have ChatGPT create commentary or conclusions for charts (e.g. “explain this trend” or “highlight anomalies”).

  • Natural language querying: Convert spoken or typed questions into DAX or SQL (e.g. “Show total revenue by region”).

  • Text analysis: Summarize survey responses, categorize invoice descriptions, or translate notes into standardized tags datatraining.io.

  • Data preparation tips: Get advice on cleaning, structuring, or optimizing data transformations. For example, ChatGPT can suggest how to split a full name column or flag duplicates.

  • Learning aid: New analysts can ask ChatGPT to explain confusing DAX code or best practices, effectively serving as an on-demand tutor indatalabs.com.

These benefits translate to faster report building and smarter results. In one example, embedding ChatGPT in Power Query allowed categorizing song lyrics by emotion – the AI labeled songs as “Romance” or “Freedom” on a word-of-mouth dataset datatraining.io. In another, an analyst passed company financial ratios into GPT and received narrative summaries of each company’s performance medium.com. Such examples show how ChatGPT can automate both routine and creative parts of the analyst’s job.

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

Before we dive into integration methods, it’s helpful to contrast Microsoft’s own AI features with what ChatGPT adds. Power BI has introduced tools like Q&A (ask a question box) and Quick Insights, and more recently “Copilot for Power BI” on Microsoft Fabric, which uses generative AI to help generate visuals and answers. However, Copilot currently focuses on basic tasks like answering simple data questions or suggesting visual elements indatalabs.com.

By contrast, integrating ChatGPT (especially GPT-4) via its API offers much greater flexibility. For example, Copilot might handle a query like “total sales by region,” but connecting ChatGPT yourself means you could build complex workflows: call external APIs (e.g. Jira, Salesforce, email), customize prompts with business context, or generate entire multi-page reports on demand. As InData Labs notes, “Copilot is currently only focused on basic scenarios for natural language queries… OpenAI GPT-4 API allows for more complex and flexible use cases” indatalabs.com. In practice, this means if you need quick, out-of-box AI help, Copilot might suffice; but for a tailored, integrated solution (like embedding AI into your ETL pipeline or combining Power BI with other services), custom ChatGPT integration is the way to go indatalabs.com.

Integrating ChatGPT in Power BI

1. Using Power Query (M) to Call ChatGPT

One powerful way to use ChatGPT in Power BI is to call the OpenAI API directly from Power Query (the data transformation layer). Here’s how it works: you write M code in the Advanced Editor that sends HTTP requests to ChatGPT and reads back the responses, all within Power BI. The steps are:

  1. Get an OpenAI API Key. Sign up at platform.openai.com and create a new secret key. The key is shown only once, so copy it now and keep it safe datatraining.io.

    Screenshot: OpenAI’s dashboard shows your secret API key (click to copy). Store this key securely for use in Power BI datatraining.io.

  2. Open Power Query’s Advanced Editor. In Power BI Desktop, go to Transform Data → Advanced Editor. Here you will paste or write M code.

  3. Insert the ChatGPT API call. For example, use code like this (replace YOUR_API_KEY with your actual key):

    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

    This M script sends the prompt to ChatGPT and retrieves the assistant’s reply. As shown above, you can define roles (system and user) and pass any question or task you like. In our example, the AI would return “Paris.” Note: if you get an error, ensure the endpoint path includes /chat/completions and the JSON syntax is correct datatraining.io.

    Screenshot: Power Query’s Advanced Editor with M code that calls the ChatGPT API. This code sets the model, prompt, and retrieves the response datatraining.io.

  4. Turn the code into a reusable function. Instead of a fixed query, refactor it into a function with parameters. For instance, you might allow any text question to be passed in. The M-language function might look like this:

    (promptText as text) =>
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 = promptText ]
        },
        max_tokens = 100
    ]),
    response = Web.Contents(apiUrl, [Headers=headers, Content=requestBody]),
    resultJson = Json.Document(response),
    resultText = resultJson[choices]{0}[message][content]
in
    resultText

    This function takes any promptText and returns the AI’s answer.

    Screenshot: Example M code in Power Query refactored as a function. Here promptText is passed to ChatGPT and the response is returned as resultText datatraining.io.

  5. Invoke the function on your data. After loading a dataset (e.g., Excel or CSV) into Power BI, add a custom column that calls your ChatGPT function for each row. For example, if you have a column of product descriptions, you could add a new column that asks ChatGPT to categorize each description. Using Add Column → Invoke Custom Function, supply the function and input column. Power BI will call ChatGPT for each row and fill in the results.

  6. Visualize the AI output. Finally, back in Report View, use the new column in your visuals. For instance, with song lyrics, one author generated a bar chart of “Song Category” (like “Romance” or “Freedom”) after using ChatGPT to assign categories datatraining.io. You could similarly create charts of AI-driven sentiment, summaries, or flags.

This Power Query approach embeds ChatGPT directly into your data transformations. It’s flexible and “incredibly powerful” for enhancing data modeling and cleansing without leaving Power BI datatraining.io. Typical business applications include sentiment analysis on text fields, auto-tagging entries, generating summaries or translations on the fly, and enriching data quality with AI datatraining.io.

2. Using Python Scripts in Power BI

Another integration path is via Power BI’s Python scripting capability. Power BI can run Python code in Power Query, allowing us to call the ChatGPT API using Python libraries like openai. The steps are:

  • Enable Python in Power BI Desktop. First, install Python (we recommend a supported version, usually 3.8 or 3.9). In Power BI Desktop, go to File → Options & settings → Options → Python scripting. Check Enable Python scripting and set the detected Python executable path (or browse to it) techcommunity.microsoft.com. Restart Power BI when prompted.

  • Install required packages. On your machine, open a command prompt or terminal and run: pip install openai pandas techcommunity.microsoft.com. This ensures you have the libraries to call the API and handle data.

  • Use “Run Python script” in Power Query. In the Power Query Editor, after loading your data table, add a new step by choosing Transform → Run Python script. This opens a script editor where your current table is available as a Pandas DataFrame named dataset (or similar).

Here’s an example Python script to call ChatGPT for each row:

import openai
import pandas as pd

openai.api_key = "YOUR_API_KEY"
# Loop through each row and create a prompt from the data
for idx, row in dataset.iterrows():
    prompt = f"Summarize the performance of {row['Company']} given these metrics: {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 script iterates over the table rows, sends a tailored prompt to ChatGPT, and writes the response into a new column called Insight. As noted in the Power BI documentation, “the Python code loops through each row of the Power BI table and uses the report data to construct the prompt… the API response is written back… one row at a time” medium.com techcommunity.microsoft.com. After running this step, your table will include the AI-generated text in the new column, ready to visualize.

Practical example: An instructor demonstrated this by taking a table of companies with financial ratios and asking GPT to describe each company’s performance. The LLM processed each row and wrote a summary sentence back into the Power BI dataset medium.com techcommunity.microsoft.com. Once the data refreshed, these narrative insights could be displayed in the report.

Note: Python scripts in Power BI run locally. If you publish the report to the Power BI Service, you will need an on-premises gateway and compatible Python environment to refresh these steps. Also, Power BI supports only certain Python packages; currently openai may need a gateway with custom configurations. Always test Python steps before relying on them in production.

3. Using Power Automate Flows with ChatGPT

You can also integrate ChatGPT using Power Automate (formerly Microsoft Flow). In this approach, a flow is triggered from a Power BI action (like a button press or data alert), calls the ChatGPT API, and returns the result to Power BI. The high-level process is:

  • Create a Power Automate flow that uses an HTTP action to call the OpenAI API with your prompt.

  • Trigger the flow from Power BI, for example via the “Power Automate for Power BI” visual or by a button on a report page.

  • Process the ChatGPT response inside the flow, then return it (e.g. via the Power BI dataset, a push dataset, or notification).

For instance, one common pattern is: user clicks a button in Power BI → the flow makes an HTTP request to the OpenAI API → ChatGPT replies → the flow inserts the answer into a Power BI field or sends it back to the report. As described by a recent guide: “The user clicks a button in the Power BI report. Power Automate launches a flow with an HTTP request to the OpenAI API. ChatGPT processes the request and returns a response. The response is displayed in Power BI as a text element or card.” indatalabs.com.

This method effectively creates a basic chatbot or Q&A interface. You might use it to add explanations on demand (e.g. “Tell me why sales dropped” pops up text), or to automate tasks like exporting insights. It is flexible (Power Automate can also call Azure Functions or other services as needed) and can be set up without modifying the Power BI dataset itself indatalabs.com.

4. Chatbots and Custom Visuals in Power BI

In some cases, you can embed a chat interface directly in your report using custom visuals. For example, Microsoft Fabric (and Power BI AppSource) includes an “LLM Chatbot” custom visual. This visual provides a built-in chat box that connects to a large language model endpoint. Users can type questions and get answers right on the dashboard. According to Microsoft’s community, this visual “seamlessly incorporates an LLM-powered Chatbot into your Power BI reports,” using the provider’s REST API and the Power BI dataset to pass context appsource.microsoft.com.

Instead of building your own, you can use the LLM Chatbot visual on AppSource. It supports different modes (general Q&A, file context, dataset context) and hides the complexity of authentication. The community notes that “you don’t need to build a custom visual from scratch – there are much easier ways to add a GPT-based chatbot to Power BI. You can use the LLM Chatbot custom visual or Power BI Embedded Analytics” community.fabric.microsoft.com. In practice, you’d drop this visual onto your report, configure it with your API key (or Azure AD token), and it will start responding to queries using your data as context.

This is ideal if you want a ready-made chat experience. Users can ask anything related to the report data (or beyond), and the answer shows up as text or cards in the report. It’s a great way to offer insights and answer ad-hoc questions in a familiar chat format.

Real-World Use Cases and Examples

Let’s look at specific examples of ChatGPT+Power BI in action:

  • Narrative Summaries: After loading sales data, you might have ChatGPT write an executive summary of key findings (“Sales increased by 15% year-over-year, with the Northeast region leading growth”). By calling GPT from Power Query or Python, the report can display these AI-generated summaries in a card or textbox. This automates what analysts often do manually: crafting conclusions from the data.

  • Data Labeling and Categorization: Suppose you have a table of customer comments or transaction descriptions. ChatGPT can categorize sentiment (“Positive”, “Negative”, “Neutral”) or tag topics. For example, Datatraining used GPT to assign one-word emotion categories to song lyrics, then visualized the categories in a chart datatraining.io. Similarly, you could categorize feedback (such as “Delivery issue,” “Product quality,” “Billing question”) without writing custom rules.

  • Automatic DAX and Query Generation: Analysts often use ChatGPT to help write complex DAX measures or SQL queries. By embedding GPT, a user could type a natural-language request like “Calculate average sales last quarter” and have it insert the DAX code. InData Labs points out that a key use is converting plain-English queries into ready-made expressions indatalabs.com. This reduces the learning curve for new analysts and speeds development for experienced ones.

  • On-Demand Recommendations: Integrating ChatGPT can power recommendation features. For instance, an interface could suggest the next best visual or highlight unusual data points (“You might compare Region versus Product Category for these trends”). ChatGPT can also generate alternative scenarios or predictive commentary when asked “what if” questions, leveraging its knowledge and any data context you provide.

  • Insights Q&A: Perhaps the most straightforward case: ask a question about the report. For example, “Why did revenue in East Region drop this month?” A ChatGPT-enabled report could call the LLM with context and return a descriptive answer. InData’s complex scenario illustrates this: a chatbot in Power BI can analyze context and answer “why revenue declined in a region”, providing a narrative that helps decision-makers indatalabs.com.

These use cases show how ChatGPT turns passive reports into interactive, intelligent tools. The insights it generates can be charted or annotated in real time. Softcraft readers might use these techniques to empower stakeholders – for instance, letting non-technical users ask their own business questions via dialog.

Best Practices, Cost, and Security Considerations

While powerful, integrating ChatGPT requires caution and planning:

  • Data Privacy: Remember that sending data to ChatGPT means it goes through OpenAI’s servers. Avoid sending highly sensitive or personally identifiable information in prompts. InData Labs advises minimizing confidential content in queries, using internal servers or filters where possible indatalabs.com. In a corporate setting (like Microsoft Fabric), ensure any integration complies with your security policies. Always anonymize or aggregate data if required before calling the API.

  • Performance and Limits: The OpenAI API has rate limits and latency. Complex reports that call GPT for many rows might be slow or hit API caps. Plan flows to limit calls (e.g., only on demand) or batch queries. Also, Power BI’s refresh cycles have timing constraints; embedding many GPT calls in a dataflow could slow development. Test performance on a small scale before rolling out broadly.

  • Cost Management: OpenAI’s services are paid per token (request/response size). As noted by InData Labs, “intensive use of ChatGPT in Power BI can lead to significant financial costs” indatalabs.com. Before scaling, estimate usage. Strategies to control cost include using smaller models for non-critical tasks, caching frequent responses, or only enabling AI on key data slices. Conduct a cost-benefit analysis: some insights may not be worth high API charges if they can be gained by simpler means.

  • Incremental Implementation: Experts recommend a gradual approach indatalabs.com. Start with a small pilot (e.g. one report or dataset) to validate the value of AI insights. Test prompts and flows on limited data. Review the results for accuracy and usefulness before integrating ChatGPT widely. This also helps your team learn how to craft effective prompts and handle responses.

  • Power BI Limitations: Keep in mind technical boundaries. For example, using Python or web calls in Power Query can prevent scheduled refreshes unless you configure a gateway. Also, as one community member noted, if Power BI cannot recognize a custom function (like Python.Execute), refreshes may fail techcommunity.microsoft.com. Ensure your chosen method (M code, Python, flow) is supported in your deployment scenario.

Additional Resources and CTAs

Ready to try it yourself? Softcraft Studio offers templates and guides to help. For instance, check out our free ChatGPT & Power BI Integration Checklist (see resources below) to get started quickly. You can also watch our companion video tutorial on embedding ChatGPT in BI dashboards for a step-by-step walkthrough.

Softcraft has an extensive Templates library (like the BI Project Checklist or Data Cleaning Checklist) that complements these methods. Consider adapting one of our general templates for BI project planning to include AI tasks. Stay tuned for Softcraft’s upcoming Power BI Generative AI Template for a ready-made example.

We also encourage you to subscribe to Softcraft Studio’s newsletter for exclusive tips and tools. You’ll get early access to new templates and tutorials, plus insider strategies for leveling up your analytics career.

Conclusion: Empowering Analysts with AI

Integrating ChatGPT into Power BI is not just a novelty – it’s a practical way to make reports smarter and more interactive. By following the steps above, even early-career analysts can harness generative AI for tasks like writing DAX, summarizing data, and answering business questions with natural language. As one guide concludes, ChatGPT “expands the possibilities for data analytics, process automation, and enhancing access to business insights” indatalabs.com. The result is analytics that are faster to build and easier to understand, democratizing insights across your team.

As AI becomes more embedded in Microsoft’s tools (like Copilot), early adopters of ChatGPT in Power BI will be ahead of the curve. Start small, experiment often, and you may find that AI integration becomes a key part of your reporting toolkit – helping you and your stakeholders make data-driven decisions with confidence.

Download our free ChatGPT-Power BI Integration Checklist

Sources: Authoritative tech blogs and tutorials were used throughout this guide ( datatraining.io indatalabs.com medium.com techcommunity.microsoft.com community.fabric.microsoft.com ) to ensure accuracy and depth of coverage.

Softcraft Studio . https://www.softcraftstudio.net/
Next

Python vs Excel: Which is Better for Data Analysis? (2025 Guide)