How to Harness the Power of OpenAI in Power BI

In the age of data-driven decision-making, combining the strengths of artificial intelligence (AI) with business intelligence (BI) tools is a game-changer. Microsoft Power BI, a leading BI platform, and OpenAI’s GPT-4, a cutting-edge language model, offer a powerful synergy when integrated. This guide walks you through the process, enabling you to leverage AI-driven insights within your Power BI reports.

The Dynamic Duo: Power BI & OpenAI

Power BI is a robust suite of tools for analyzing, visualizing, and sharing data-driven insights. OpenAI’s GPT-4 excels at understanding and generating human-like text, making it ideal for complex tasks like summarization, analysis, and even content creation. Integrating these platforms unlocks a new level of analytical depth.

Important Note: This integration utilizes OpenAI’s API, which requires careful consideration of data privacy and security. Be sure to review OpenAI’s data usage policy: https://platform.openai.com/docs/data-usage-policy

Prerequisites:

• Power BI Pro or Premium account
• OpenAI API Key (obtain one at: https://beta.openai.com/signup/)

Step 1: Python Setup

Begin by ensuring Python and essential libraries are installed:

1. Download Python: Get the latest version for your OS from python.org.
2. Install:

• Windows: Check “Add Python to PATH” during installation.
• macOS: Follow the installer prompts.
• Linux: Python is usually pre-installed; update via your package manager.
• Verify: Open a command prompt/terminal and type python --version or python3 --version.
• Libraries: Install these via pip

pip install openai pandas powerbiclient msal

Step 2: Prepare Your Power BI Dataset

1. Login: Access the Power BI service at app.powerbi.com.
2. Datasets: Go to your workspace and click “Datasets + dataflows.”
3. Create: Click “+ Create” and select “Streaming Dataset.”
4. API: Choose “API” as the connection type and proceed.
5. Details: Name your dataset (e.g., “OpenAI_Insights”) and create it.
6. Save: Note down the provided API URL and authentication token.

Step 3: The Python Integration Script

1. Create Script: Make a new file (e.g., openai_powerbi_integration.py).
2. Imports:

import openai
import pandas as pd
from powerbiclient import Report, models
import requests
from msal import PublicClientApplication

3. API Keys: Replace placeholders with your actual keys.

openai.api_key = "your_openai_api_key" 
POWER_BI_CLIENT_ID = "your_power_bi_client_id"

4.OpenAI Function:

def fetch_openai_data(prompt):
    # ... (OpenAI API call logic)

5.Power BI Function:

def push_data_to_powerbi(api_url, auth_token, dataframe):
    # ... (Power BI data push logic)

Step 4: Run & Visualize

1. Execute: In your terminal, run python openai_powerbi_integration.py.
2. Power BI:

• Return to the service.
• Open the “OpenAI_Insights” dataset.
• Create a new report.
• Visualize the “OpenAI_Insight” data using tables, charts, etc.

Here is the full code

import openai
import pandas as pd
from powerbiclient import Report, models
import requests
from msal import PublicClientApplication

# Set up OpenAI API key
openai.api_key = "your_openai_api_key"

# Set up Power BI authentication
POWER_BI_CLIENT_ID = "your_power_bi_client_id"
AUTHORITY = "https://login.microsoftonline.com/common"
SCOPE = ["https://analysis.windows.net/powerbi/api/.default"]
app = PublicClientApplication(POWER_BI_CLIENT_ID, authority=AUTHORITY)

result = None
accounts = app.get_accounts()
if accounts:
    result = app.acquire_token_silent(SCOPE, account=accounts[0])

if not result:
    flow = app.initiate_device_flow(scopes=SCOPE)
    print(flow["message"])
    result = app.acquire_token_by_device_flow(flow)

powerbi_auth_token = result["access_token"]

# Function to fetch data from OpenAI
def fetch_openai_data(prompt):
    response = openai.Completion.create(
        engine="gpt-4-0613",
        prompt=prompt,
        max_tokens=100,
        n=1,
        stop=None,
        temperature=0.7,
    )
    generated_text = response.choices[0].text.strip()
    return generated_text

# Function to push data to Power BI
def push_data_to_powerbi(api_url, auth_token, dataframe):
    headers = {
        "Content-Type": "application/json",
        "Authorization": f"Bearer {auth_token}",
    }
    data_json = dataframe.to_json(orient="records")
    response = requests.post(api_url, headers=headers, data=data_json)
    return response.status_code

# Define your OpenAI prompt
prompt = "Summarize the key factors affecting the global economy in 2024."

# Fetch data from OpenAI
openai_data = fetch_openai_data(prompt)

# Create a DataFrame with the data
data = {"OpenAI_Insight": [openai_data]}
dataframe = pd.DataFrame(data)

# Power BI API URL (replace with your actual API URL)
api_url = "your_power_bi_api_url"

# Push data to Power BI
status_code = push_data_to_powerbi(api_url, powerbi_auth_token, dataframe)

# Print status code for confirmation
print(f"Data push status code: {status_code}")

Let me know if you’d like help refining any specific part of the script or the Power BI report creation process!

Ping me if you are interested in a fully functional project along with PowerBI files.

https://www.linkedin.com/in/shahzadasghar1/