ChatGPT — How to Use it With Microsoft Power BI
--
Turbocharge your Power BI reports by using Python and the gpt-3.5-turbo API from OpenAI.
ChatGPT from OpenAI
OpenAI has recently announced that it will support third-party developers to integrate ChatGPT into their apps and services via an API. According to OpenAI, its new model family, called GPT 3.5 Turbo, is the “best model for many non-chat use cases.”
At the time of writing, OpenAI is offering 1,000 tokens for $0.002, which it claims is “10x cheaper than our existing GPT-3.5 models,” thanks in part to “a series of system-wide optimizations.”
OpenAI claims that its ChatGPT API can be used to directly inject AI capability into a host of existing tools and technologies, using the ubiquitous python programming language.
This combination of an extremely low barrier to entry and ease of integration with existing applications opens up a world of opportunity to embed AI everywhere.
This article will explore the use of the gpt-3.5-turbo API in a “one-shot” modality, to leverage the AI capability of the GPT large language model against data rendered in a Power BI report.
Power BI from Microsoft
Power BI is a comprehensive business intelligence and data visualization tool that provides numerous benefits to users across various industries.
Power BI’s popularity and market share are due to several factors. Firstly, its integration with other Microsoft products, such as Excel and SharePoint, makes it easier for organizations already using Microsoft products to adopt Power BI. Secondly, it is relatively easy to use with users creating reports and dashboards without extensive technical expertise.
Power BI already has several inbuilt AI capabilities that help users analyze and visualize data more efficiently. These capabilities include Quick Insights, Smart Data Discovery, Natural Language Querying, AI-powered visuals, and Automated Machine Learning.
The ability to extend these capabilities by integrating with ChatGPT via Python opens up a novel and exciting way to analyze existing reporting content.
How to use ChatGPT API
Using the ChatGPT API involves a few steps:
- Obtain an API key: In order to use the ChatGPT API, you will need to sign up for an API key. This can be done through the OpenAI website.
- Choose a programming language: The ChatGPT API has SDKs and libraries available in several programming languages, these examples will use Python.
- Install the SDK: Once you have chosen a programming language, you will need to install the openai library for that language. This can usually be done using a package manager, such as pip
4. Create a new instance of the API: Once you have installed the SDK, you can create a new instance of the API by providing your API key and any other necessary configuration options. I recommend that you test this in a Python IDE first eg. Visual Studio Code.
5. Make requests to the API: Once you have an instance of the API, you can start making requests to it. For example,
6. Process the response: Once you receive a response from the API, you can process it as needed. For example, you might extract the generated text from the response and display to check that it works.
Enabling Python in Power BI Desktop
If not already enabled, to enable Python in Power BI Desktop, follow these steps:
- Install Python: You need to have Python installed on your computer. You can download and install Python from the official website (https://www.python.org/downloads/).
- After installing Python, you need to install the Python integration feature in Power BI Desktop. To do this, open Power BI Desktop and go to File -> Options and settings -> Options -> Python scripting. Check the box “Python scripting” and click “OK”. This will enable Python scripting in Power BI Desktop.
- After enabling Python scripting, you need to set the Python path in Power BI Desktop. To do this, go to File -> Options and settings -> Options -> Python scripting. Click on “Detect” to automatically detect the Python installation path or you can manually set the path by clicking on “…” and selecting the Python executable file.
- Restart Power BI Desktop: After setting the Python path, you need to restart Power BI Desktop for the changes to take effect and you good to go.
Use Case — Financial Analysis of Company Ratios
In this example I have an existing Power BI report that contains a table of Financial Ratios for a select group of companies that I am interested in analyzing in detail. We are going to use the analytical power of the large language model developed by OpenAI to interpret and describe each company’s performance based on these metrics. We will pass these financial performance KPIs as raw data to ChatGPT and ask it to return a narrative that describes each company’s performance.
My dataset looks like this -
Calling the ChatGPT API from Power BI Desktop
Within the Power Query editor, use the Run Python script feature available from the Transform Ribbon. This will allow you to insert a step to call the ChatGPT API at the end of an existing data transformation.
Python code to call the gpt-3.5-turbo API -
Note in the code above, that the ‘dataset’ is a built-in pandas dataframe like structure which allows the Power BI developer to access and manipulate data from the Power BI table using Python. The Python code loops through each row of the Power BI table and uses the report data to construct the prompt for ChatGPT. This prompt is passed to ChatGPT with the API response being written back to the Power BI dataframe and table one row (Company) at a time.
Power BI Report with ChatGPT response rendered in a visual.
Once the response from the API is within the Power BI table it can be easily rendered into an existing report or dashboard.
Conclusion
There you have it! This method can be adapted to pass a variety of data that you may already have within existing Power BI reports and leverage the insight of the emerging AI from OpenAI’s large language models.
Clap it if you find this helpful or feel free to leave your questions;)
