What is the PowerBI M Query to connect to openai API?

API
1

Hello All,

I’m using the below PowerBI M query to ask a simple question to test it, however I’m not sure whether it is correct because I always receive the message
An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from ‘…api.openai.com/v1/completions’ (429): Too Many Requests.

Could you review this PowerBI query M code?

let
// Define the request parameters
apicontent = “what is cloud”,
apikey = “api secret key”,
openai_url = “…api.openai.com/v1/completions”, // replaced https to … to post it here
openai_headers = [#“Authorization” = “Bearer " & apikey, #“Content-Type” = “application/json”],
openai_payload = “{”“model””: ““gpt-3.5-turbo””, ““prompt””: “”" & apicontent & “”“, ““max_tokens””: 100}”,
openai_params = [Headers = openai_headers, Content = Text.ToBinary(openai_payload)],

// Send the HTTP POST request to the OpenAI API endpoint and parse the response as JSON
response = Web.Contents(openai_url, openai_params),
statusCode = Value.Metadata(response)[Response.Status],
content =
    if statusCode = 200 then
        Json.Document(response)
    else if statusCode = 404 then
        "Not found"
    else if statusCode >= 500 then
        "Server error"
    else
        null

in
// Return the response content
content[choices]{0}[text]

Thank you

1 Like
2

Welcome to the forum.

Sounds like you’re running into rate limits on your account? What Tier are you?

2 Likes
3

Hello Paul, Great… thank you for pointing that out. I just moved account to Tier 1… but now I have a different error:

DataSource.Error: Web.Contents failed to get contents from ‘…api.openai.com/v1/completions’ (404): Not Found
Details:
DataSourceKind=Web
DataSourcePath=…api.openai.com/v1/completions
Url=…api.openai.com/v1/completions

Not sure what is the error because everything looks fine? Please, could you suggest what could be now?

PS: replace the https:// above with … to be able to post the message.

Thank you again. Marcio

1 Like
4

Nice! Good to hear.

Let’s crack on the second one…

Be sure to look at model endpoint compatibility…

Basically, there’s two endpoints now…

Completion (Legacy)
Chat Completion (ChatML)

You send a simple prompt to the first and a messages object to the second.

The latest model for Legacy Completion would be gpt-3.5.-turbo-instruct, I believe. We’re trying to talk OpenAI into a GPT-4-instruct, but no word yet.

5

Hello Paul,

Have tried a different code and it worked perfect!.

Please, find it for your reference and to support people in the community.

let
Output = (prompt as text) as text =>
let
url = “…api.openai.com/v1/chat/completions”, // replaced “https://” to “…” to be able to post here
apiKey = “secret key”,
headers = [
#“Content-Type” = “application/json”,
#“Authorization” = "Bearer " & apiKey
],
body = Json.FromValue([model = “gpt-3.5-turbo”, messages = {[role=“user”, content = prompt]}]),
response = Web.Contents(url, [Headers=headers, Content=body]),
content = Json.Document(response)[choices]{0}[message][content]
in
content
in
Output

Thank you againg and Regards, Marcio