How to Integrate ChatGPT in Power BI

Combining the analytical power of Power BI with the language capabilities of ChatGPT can completely change how you create reports and analyze data. This integration allows you to automatically generate text summaries, craft complex DAX formulas from plain English, and add narrative context to your dashboards. This guide will walk you through the entire process, from setting up the connection to exploring practical ways to use it.

Why Integrate ChatGPT with Power BI?

Connecting these two tools moves your dashboards from static charts to dynamic, conversational reports. Instead of just showing what happened, you can get instant explanations for why it might have happened. This integration helps bridge the gap between raw data and actionable business insights.

Here are a few key benefits:

• Automated Report Summaries: Imagine your monthly sales report automatically writing its own executive summary. You can feed data from your visuals directly into ChatGPT to generate concise, human-readable summaries that highlight key trends, saving you hours of manual interpretation and writing.
• Natural Language-Powered DAX: Writing Data Analysis Expressions (DAX) can be challenging. With this integration, you can simply describe what you need - like "calculate year-over-year sales growth" - and have ChatGPT generate the correct DAX formula for you right inside Power BI.
• Dynamic Data Explanations: When a user filters a report, the context can change dramatically. You can use this connection to generate new explanations on the fly, explaining what the data in the filtered view means. It’s like having a data analyst built into every report you create.
• Data Categorization and Enrichment: Have a column of unstructured text, like customer feedback? You can send that data to ChatGPT to perform sentiment analysis, extract keywords, or categorize comments, adding new, structured dimensions to your dataset for better analysis.

What You’ll Need to Get Started

Before we jump into the steps, let’s make sure you have everything you need. The setup is straightforward and doesn’t require deep technical knowledge, but you will need a few things:

• Power BI Desktop: This integration is configured in Power BI Desktop. If you don't already have it, you can download it for free from Microsoft.
• An OpenAI Account: You need an account with OpenAI to access their API. If you don't have one, you can sign up on the OpenAI website.
• An OpenAI API Key: Once you have an account, you'll need to generate a secret API key. This key is used to authenticate your requests from Power BI. Keep in mind that using the API is a paid service, but it's typically very affordable for reporting purposes.

Step-by-Step Guide: Connecting ChatGPT to Power BI

The connection is made through Power Query, Power BI's data transformation engine. We'll create a custom function that sends a prompt to the OpenAI API and returns ChatGPT’s response. Let's walk through it.

Step 1: Get Your OpenAI API Key

Your API key is the password that allows Power BI to talk to ChatGPT. Keep it safe and never share it publicly.

1. Log in to your account at https://platform.openai.com/.
2. Click on your profile icon in the top-right corner and select "View API keys."
3. Click the "Create new secret key" button. Give your key a recognizable name (e.g., "PowerBI_Key") and click "Create secret key."
4. Copy the key immediately and save it somewhere secure, like a password manager. You will not be able to see this key again after closing the window.

It's also a good idea to set up billing and usage limits in your OpenAI account dashboard to avoid any surprise charges.

Step 2: Create a Custom Function in Power Query

Now we'll head into Power BI to build the function that handles the API communication. This piece of M code will be our bridge to ChatGPT.

1. Open Power BI Desktop and click Transform data from the Home ribbon to open the Power Query Editor.
2. In the Power Query Editor, go to the Home ribbon, click New Source, and select Blank Query.
3. With the new query selected, click on Advanced Editor from the Home ribbon.
4. Delete any existing text in the editor and paste the following M code. This code defines a function that takes a text prompt as input and sends it to the OpenAI API.

(prompt as text) as text => let
    // OpenAI API details
    ApiKey = "YOUR_API_KEY_HERE",
    Endpoint = "https://api.openai.com/v1/chat/completions",

    // Request body
    RequestBody = Json.FromValue(
        [
            model="gpt-3.5-turbo",
            messages = {
                [
                    role="user", 
                    content=prompt
                ]
            },
            temperature=0.7,
            max_tokens=500
        ]
    ),

    // Web request details
    Request = Web.Contents(
        Endpoint,
        [
            Headers = [
                #"Content-Type"="application/json",
                #"Authorization"="Bearer " & ApiKey
            ],
            Content=RequestBody
        ]
    ),

    // Process the response
    ResponseJson = Json.Document(Request),
    GeneratedText = ResponseJson[choices]{0}[message][content]
in
    GeneratedText

Step 3: Secure Your API Key and Finalize the Function

Hardcoding your API key directly into the function is a security risk, especially if you plan to share the .pbix file. Let's use a Power BI parameter to store it securely.

1. Replace "YOUR_API_KEY_HERE" in the code with your actual OpenAI API key for now. Click Done.
2. Rename the query to something memorable, like InvokeChatGPT. You'll see an interface appear where you can test the function. Try entering a prompt like "What is Power BI?" and click Invoke to make sure it works.
3. Now, let’s secure the key. In Power Query's Home ribbon, click Manage Parameters > New Parameter.
4. Name the parameter OpenAIKey.
5. Go back to your InvokeChatGPT function and open the Advanced Editor again.
6. Find the line: ApiKey = "YOUR_API_KEY_HERE",
7. Change it to: ApiKey = OpenAIKey,
8. Click Done. Your function will now reference the parameter instead of the hardcoded key. When you publish your report, you can manage this parameter's value in the Power BI service without exposing the key.

Once you're done, click Close & Apply in the top-left corner of the Power Query Editor.

Putting It to Work: Practical Examples

With the connection established, you can now call this function on any data table in your model. Let's look at a few powerful ways to use it. For these examples, imagine you have a simple table named Sales Data with columns like Product, Region, and Sales.

Example 1: Generate a Dynamic Summary of Monthly Sales

Let's create a concise, written summary of your top-performing products for the month.

1. First, prepare the data. You don't want to run the function on thousands of individual sales rows, as that would be slow and costly. Create a summary table in Power BI that shows total sales by product.
2. Convert this summarized data into a single text string. You can do this by creating a new measure or column that concatenates the product names and sales figures into a simple format like: "Product A: $5000, Product B: $4200, Product C: $3800...".
3. Next, in the Power Query Editor, select your data source. Go to the Add Column tab and click Invoke Custom Function.
4. Select your InvokeChatGPT function from the dropdown. For the prompt argument, instead of static text you will structure a dynamic prompt. Select the column containing your concatenated text string. Then you'll need to write a little M code to add your full prompt. A prompt could be: "Summarize the following sales as an analyst would. Identify the top 3 key takeaways: " & [YourDataColumn].
5. Click OK. Power Query will now add a new column. For each row, it will send the prompt with the sales data to ChatGPT and return the AI-generated summary.

Example 2: Create DAX Measures with Natural Language

Are you tired of searching for complex DAX syntax? Let ChatGPT write it for you.

1. In Power BI, click on Enter Data in the Home ribbon to create a new table.
2. Create a single column named Request. In the rows, type what you want the DAX formula to do. For example:
3. Load this new table and open the Power Query Editor.
4. With the new table selected, go to Add Column > Custom Column.
5. Name the new column DAX_Formula.
6. In the formula box, create a detailed prompt that calls your function. The key here is providing context about your data model. Your formula would look something like this:

InvokeChatGPT(
"Write a DAX formula to " & [Request] & ". Assume a sales table exists named 'FactSales' with columns 'OrderDate' [date] and 'TotalRevenue' [currency]."
)

1. A new column will appear with the exact DAX formula requested. All you have to do is copy it and create a new measure in your Power BI model.

Important Considerations and Best Practices

While this integration is incredibly powerful, there are a few things to keep in mind for responsible and efficient use.

• API Costs: Every time your dataset refreshes, Power BI will re-run the InvokeChatGPT function for each row, which triggers a call to the OpenAI API. Monitor your usage in the OpenAI dashboard to ensure costs stay within your budget. For large datasets, consider only refreshing the data periodically.
• Report Performance: API calls take time. Calling the function on hundreds or thousands of rows will significantly slow down your report's refresh speed. Always try to run the function on summarized or aggregated tables, not on your raw transactional data.
• Data Privacy: This is the most crucial point. Do not send Personally Identifiable Information (PII) or any sensitive company data to the OpenAI API. You are transmitting a copy of your data over the internet to a third-party service. Use this integration for non-sensitive, general business data only.
• Hallucinations and Accuracy: ChatGPT is a creative language model, not a calculator. While it’s excellent for text and code generation, it can sometimes make mistakes or "hallucinate" information. Always review its output, especially DAX formulas and analytical summaries, to confirm they are accurate and make sense in your business context.

Final Thoughts

Integrating ChatGPT with Power BI offers a powerful way to automate reporting tasks, generate human-readable insights, and make advanced analytics more accessible. By creating a simple custom function in Power Query, you can unlock a suite of AI capabilities directly within your dashboards, saving time and enriching your data stories.

While setting up this connection manually provides amazing flexibility, we know it can still be tricky to manage API keys, costs, and performance. At https://www.graphed.com/register, we created a platform that handles all of this for you. We allow you to connect your data sources in seconds and use simple, natural language to build dashboards, generate reports, and get AI-powered insights instantly - no code or complex setup required. Our approach is to remove the technical hurdles so you can focus on asking questions and getting answers.