Data is the new currency of the modern world, and to succeed in this competitive field, you need to master data concepts and tools.
This blog will show you how artificial intelligence (AI), especially Azure Open AI, can transform your data decision-making process within Power BI. Azure Open AI is a cloud-based AI service that uses pre-trained models to generate new content. You can pay for the service with tokens and benefit from features like cloud security and identity management.
Azure Open AI has many applications, including content generation, sentiment analysis, machine translations, and automation. Whether you are a business analyst, a researcher, or a data enthusiast, Azure Open AI can help you achieve your data goals. Join us as we dive into these fantastic possibilities in the next section.
Creating Azure OpenAI involves a series of steps that build on the principles of Cloud Native architecture. Here's a simplified guide on how to create Azure OpenAI:
The Azure OpenAI Service Building Blocks are the core components and tools for creating and managing generative AI solutions. They include:
In this blog, we will focus on using the Visual Interfaces of the Azure OpenAI Service, namely Azure OpenAI Studio and Playground, which offer user-friendly interfaces for testing, customizing, and deploying AI models without coding.
Azure OpenAI Playgrounds are applications within the service that let users interact and customize various AI models, including chat-based models like ChatGPT, non-chat language models, and image generation models. These interfaces simplify generative AI models, especially for non-technical users.
You can access each Playground (and their related management features) from the left panel of the Studio or visit them directly by following URLs linked in the following sections.
The Chat Playground is a feature within the Azure OpenAI Studio. It provides an interactive interface for users to test and configure the behavior of the chat-based AI models.
The main components of the Chat Playground include:
Deployments provide endpoints to the Azure OpenAI base models, or your fine-tuned models, configured with settings to meet your needs, including the content moderation model, version handling, and deployment size. You can view your deployments from this page, edit them, and create new ones.
You need an endpoint and a key to make a call successfully against Azure OpenAI.
This value can be found in the Keys & Endpoint section when examining your resource from the Azure portal. Alternatively, you can find the value in the Azure OpenAI Studio > Playground > Code View. An example endpoint is https://xxxxxxxxxx.openai.azure.com/.
We can copy the Python code from the Playground to use our model API in our Python code (JSON and CURL are also available). This code will import the required library and export our parameter settings.
We will also create a Power BI report and use Azure Open AI to generate insights from the data. Before we begin, we need to complete some steps:
Pip install openai
2. Now, we need to create a dataset to analyze. Follow these steps:
1. Import your data and choose Transform Data
2. You can check the column quality to see if there is something wrong with data quality or empty cells, which can lead to false insight
3. Add Run Python script to the Applied Steps section.
4. Now, we can run the code in the next section directly into Power BI.
1. To start, import the necessary Python libraries
# Import Libraries
# Set OpenAI API for Azure
openai.api_type = "azure"
openai.api_base = "https://xxxxxxxxxx.openai.azure.com/"
openai.api_version = "2023-07-01-preview"
openai.api_key = "xxxxxxxxxxxxxxxxxxxxxx"
# Loop through each row in Renamed Columns & concatenate the data into a single string. Pass resulting string to the API
for index, row in Renamed_Columns.iterrows():
messages = [
"content": "For each company, I will give you the followwing information: Company, Price to Earnings, Price to Book, Return to Equity%, Debt to Equity, Current Ratio, Gross Margin%. Then, I will analyze the ratios and write a brief summary using the company name "
"content": ''.join([str(col) for col in row])
# Make a chat completion Request for the API
chat = openai.ChatCompletion.create(
engine = "GPT-35-turbo",
messages = messages,
temperature = 0.7,
max_tokens = 800,
top_p = 0.95,
frequency_penalty = 0,
presence_penalty = 0
# Process the response from API
reply = chat.choices.message.content
# Write the response Back to the Report
Renamed_Columns.at[index, "reslt"] = reply
This script runs a Python code that iterates over the Power BI table rows and builds a prompt for ChatGPT using the report data. The prompt is sent to ChatGPT, and the API response is returned to the Power BI DataFrame and table for each row (company).
2. Expanding the table means turning a column with tables inside it into multiple columns and rows. This can help you flatten your data and make it easier to analyze.
In the Power BI desktop, you can use the slicer visual to select the company and the table visual to view the generative insight of the data according to the field you choose.
Congratulations on completing this tutorial blog on How to use Azure Open AI to Enhance Your Data Analysis in Power BI; you have learned how to use Azure Open AI to enrich your data analysis in Power BI. You have discovered how Azure Open AI can create new content based on the dataset using a Python script. Azure Open AI is a versatile tool that can help you leverage your data and make smarter decisions. Whether you are a business analyst, a researcher, or a data enthusiast, Azure Open AI can help you gain data skills and insights. We hope you found this blog helpful and informative.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.