🚀 Powering Your Dashboards with OpenAI API
A Power BI custom visual for NLP analysis of your data
Github repo: link
1) Introduction: 🏁
👋 Hey there, data enthusiasts, Pokémon trainers, and everyone in between! I’ve got something exciting to share. Now, we all love data and insights, right? But, wouldn’t it be cool if we could have a conversation with our data? 😱 … Well, buckle up because that’s exactly what I’ve been working on. 🚀
I’ve created a PowerBI custom visual that uses the OpenAI API to perform Natural Language Processing (NLP) on your data. Yes, you heard it right. We’re about to turn PowerBI into a mini conversation hub. 🗣️📊
Now, I didn’t do this all by myself. I had some help from a very special AI, OpenAI’s chat GPT. This AI has been my coding partner, guiding me through the process and helping with the code. So, all credit to my AI comrade for the support. 🤖💻
1️⃣ This post will dive into how this custom visual works using a dataset we all love — Pokémon! But, the fun doesn’t stop there. This approach can be applied to any PowerBI dashboard, unlocking a whole new level of interactivity and understanding. Let’s dive in! 🏊♂️
2) How to install and use this code to analyze your data🚀
1 — Download the Repository: Let’s start our journey by grabbing the project from GitHub. Make sure you have Git installed locally. Use this command to clone the repository:
git clone https://github.com/jegamboafuentes/powerBIVisual_NLP_by_OpenAIGPTThen, navigate into your brand new project directory:
cd powerBIVisual_NLP_by_OpenAIGPT2 — Create a config.json File: Next, in the project directory, create a config.json file on the root folder. It's like the ID card for your project, telling it how to connect with OpenAI:
{
"OPENAI_API_KEY": "*-*your-openai-key*-*"
}Don’t forget to replace *-*your-openai-key*-* it with your actual OpenAI API key. It's like the secret handshake between your project and OpenAI. 🤝
3 — Package the Custom Visual for PowerBI Desktop: Now, it’s time to package the custom visual. It’s like wrapping a gift before you give it to someone. Run this command:
pbiviz packageThis will create a dist/ folder. To import the custom visual into PowerBI Desktop, follow these steps:
- Open PowerBI Desktop.
- Go to
Home > Visualizations > Import a custom visual > Import from file. - Navigate to the
dist/folder, and select the.pbivizfile.
Congratulations! 🎉 You’ve successfully installed the custom visual in PowerBI Desktop!
⭐ Now, scroll some data to the visual and ask questions! 🏆
3) How to make changes to this Power BI custom visual🔧
1 — Customize and make code changes: Ready to make this project your own? Run this command:
pbiviz start2 — Test the Changes in PowerBI.com: To see your changes in action, follow these steps:
- Head over to PowerBI.com and sign in.
- Enter your workspace, then click on
Settings. - Open the
Developersection and flip the switch to enable theDeveloper visualssetting. - Go back to your report, and look! There’s the
Developertab in theVisualizationspane. 🎊
The custom visual will automatically sync with the changes you’ve made locally. Now you can tweak and personalize the custom visual, testing it all the while in PowerBI.com. It’s like having your own personal AI-powered sandbox. 🏖️
4) How this Power BI custom visual works 🛠️💡
Now, let’s dive into the heart 🖤 of this custom visual and understand how it works! The crux of the logic lies in a file name visual.ts that's been developed in javascript. Here, we'll particularly focus on two critical parts: the getGptResponse(question: string) function and the HTML front-end code.
The function that calls OpenAI API📖
The getGptResponse(question: string) function is the backbone of the NLP analysis, and it's where the magic happens! 🪄This function communicates directly with the OpenAI API to fetch insightful responses to the questions you pose about your data.
The function begins by setting the necessary parameters: the OpenAI API key, the API URL, the model used is (text-davinci-003 [GPT3]).
private async getGptResponse(question: string): Promise<string> {
const apiKey = config.OPENAI_API_KEY;
const apiUrl = "https://api.openai.com/v1/completions";
const model = "text-davinci-003";
const prompt = `You are a Data Analyst that gives short answer questions about data.\n Question:${question}\n Dataset: ${this.formatDataForGpt()}\n\n Answer:`;
console.log(prompt);
const response = await fetch(apiUrl, {
method: "POST",
headers: {
"Content-Type": "application/json",
"Authorization": `Bearer ${apiKey}`
},
body: JSON.stringify({
model,
prompt,
max_tokens: 50,
n: 1,
stop: null,
temperature: 0.5
})
});The prompt is what guides the OpenAI model to understand the context of the question and generate a suitable response. In this case, the prompt is set as:
`You are a Data Analyst that gives short answer questions about data.\n
Question:${question}\n Dataset: ${this.formatDataForGpt()}\n\n Answer:`This prompt tells the AI that it’s role-playing as a data analyst who gives short answers to questions about a given dataset. The question and Dataset are dynamically inserted into the prompt based on the input from the user and the connected dataset.
You can customize this prompt to suit your needs and the context of your data. Just remember how you frame the prompt will directly impact the output. So, experiment away and find the best fit for your dashboard!
Front-end HTML Code 🖥️
The front-end interface of this custom visual is primarily driven by HTML code. This code is what builds the visual’s user interface, including the input box for the question and the submit button. The relevant code snippet is as follows:
this.target.innerHTML = `
<div>
<h1>🔍 NLP Analysis tool</h1>
<h3>Ask questions about data</h3>
<h6>Powered by OpenAI GPT API ⚡</h6>
<textarea id="gptQuestionInput" type="text" placeholder="Ask a question about the data" /></textarea>
<br>
<button id="gptSubmitButton">Submit</button`This HTML block creates the interactive parts of the visual, including a header for the tool, the input field for the user to ask their question, and a ‘Submit’ button to send the question to the OpenAI API.
And the best part? The front-end code is flexible and can be customized with JavaScript and CSS! You can modify this HTML code to suit your dashboard’s look and feel, or add new features as per your requirements.
So, there you have it! This is how the custom visual works and integrates the OpenAI API to perform NLP analysis on your data. Isn’t it exciting to see the power of AI being harnessed directly in PowerBI? Now, it’s your turn to make the most of it. Happy free-ish Power BI NLP analyzing! 🎉💼🔍
5) How to make your own Power BI custom visual from scratch
If you’re feeling a bit adventurous 🧗♂️ and want to create your own PowerBI custom visual, I’ve got some tips for you.
👉 Step 1: Get acquainted with PowerBI custom visuals: Start by understanding what a PowerBI custom visual is. Microsoft has a handy guide on how to develop a PowerBI custom visual. Trust me; it’s a good read. It’ll take you through the process, step-by-step. Like a friendly tour guide, but for coding. 🗺️💻
👉 Step 2: Familiarize yourself with the OpenAI API: Next, get to know the OpenAI API. OpenAI has excellent documentation that explains how to make API calls, the kind of data it can process, and the limits you need to keep in mind. It’s like getting to know a new friend, just a bit more technical. 🤖📚
👉 Step 3: Coding time: Now, it’s time to get your hands dirty with some coding. Build your custom visual by following the Microsoft guide, and then integrate it with the OpenAI API. Don’t forget to keep an eye on the data size limits of the API. And remember, chat GPT, Google, and Stack Overflow are your best friends in this journey. 🛠️👨💻
👉 Step 4: Test, test, and test some more: Once you’ve got your custom visual ready and integrated with the OpenAI API, it’s time to test it out. Use different datasets, ask various questions, and see how your visual performs. Don’t be afraid of bugs. They’re just opportunities for improvement. 🐞🔍
👉 Step 5: Iterate and improve: Based on your testing, make necessary changes to your custom visual. Remember, Rome wasn’t built in a day. It’s all about iterating and improving. 🔄👍
Now, you might be wondering, “How am I going to do all this by myself?” 😱Don’t worry, you’re not alone. My trusty AI friend, OpenAI’s ChatGPT, was there for me throughout this journey, providing guidance, answering questions, and even helping with the code. It can do the same for you. So, don’t hesitate to call upon ChatGPT if you need an AI buddy for your coding adventure. 🤝🤖
There you have it! Your guide to creating your own PowerBI custom visual and connecting it to the OpenAI API. Remember, it’s a journey, and every journey begins with a single step. So, take that step and start your adventure in the world of data and AI. Happy coding! 🌍🔭
6) Limits of this custom visual and next steps
Now, as much as I’d love to say that this custom visual can analyze all the data in the world in one go, we do have some constraints. The input for this custom visual is currently limited to fit within the data size that the OpenAI API can handle. But hey, every superhero has its kryptonite, right? 🦸♂️💥
But don’t let that get you down. I encourage you to push these boundaries. Let’s make the most of what we have and look for innovative ways to break these limits. 🚀
This is just the start of our journey. The world of data and AI is vast and unexplored, and I can’t wait to see where we go next. So, stay tuned for more adventures in data land! 🌍🔭
Reference:
- Microsoft. (n.d.). Develop Power BI visuals. Microsoft Learn. Retrieved May 23, 2023, from https://learn.microsoft.com/en-us/power-bi/developer/visuals/develop-power-bi-visuals
- OpenAI. (n.d.). API reference. OpenAI Platform Documentation. Retrieved May 23, 2023, from https://platform.openai.com/docs/api-reference
- Some Guy Named Chris. (2023, March 10). ChatGPT — Power BI Custom Visual | SHOCKED AND CONFUSED [Video]. YouTube. https://www.youtube.com/watch?v=q1XszZrZ3es
Github repo: link
Pokemon dataset: link
