Python in Power Query Script to Pull Current List of Open AI Model IDs

gistfile1.txt

let
    Source = "
import os
import openai
import pandas as pd
openai.api_key = os.getenv(""OPEN_AI_POWER_QUERY_API_KEY"")
response = openai.Model.list()
df = pd.DataFrame(response['data'])
",
    RunPyScript = Python.Execute(Source),
    Expand = Table.ExpandTableColumn(RunPyScript, "Value", {"id", "object", "owned_by"}),
    RemoveCols = Table.RemoveColumns(Expand, {"Name"}),
    Filter = Table.SelectRows(RemoveCols, each ([owned_by] = "system")),
    Sort = Table.Sort(Filter,{{"id", Order.Ascending}})
in
    Sort

/*
DETAILED EXPLANATION:

1. Source:
   - Defines a Python script as a multi-line string
   - Imports required libraries (os, openai, pandas)
   - Sets the OpenAI API key from an environment variable
   - Retrieves a list of available OpenAI models using the API
   - Converts the response data to a pandas DataFrame

2. RunPyScript:
   - Executes the Python script using Power Query's Python.Execute function
   - Returns a table with the results from the Python execution

3. Expand:
   - Uses Table.ExpandTableColumn to extract specific columns from the nested table
   - Extracts "id", "object", and "owned_by" fields from the "Value" column

4. RemoveCols:
   - Removes the "Name" column from the expanded table
   - Keeps only the columns that were expanded in the previous step

5. Filter:
   - Applies a filter to only keep rows where "owned_by" equals "system"
   - This filters out models that aren't owned by OpenAI's system

6. Sort 
    - This sorts the id field in ascending alphabetical order

The final result is a filtered, sorted table containing only system-owned 
OpenAI models with their id, object type, and ownership information.

Authored by Brian Julius, March 2025

*/