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

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?

// 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
    else if statusCode = 404 then
        "Not found"
    else if statusCode >= 500 then
        "Server error"

// Return the response content

Thank you

1 Like

Welcome to the forum.

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


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

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

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.

Hello Paul,

Have tried a different code and it worked perfect!.

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

Output = (prompt as text) as text =>
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]

Thank you againg and Regards, Marcio