M Language Function to Call Open AI API from Power Query
I’ve published a new version of the fxOpenAI function for Power Query.
https://github.com/avatorl/M/blob/master/fx/fxOpenAI.m
How to use
= fxOpenAI("user prompt", "system prompt", "gpt-4o")
= fxOpenAI("user prompt")
System prompt is optional. Default system prompt: “You’re a helpful assistant”
Model code is optional. Default model: “gpt-4o-mini”
To use the function, replace <OPENAI_API_KEY> with your actual API key:
Structured Output
This ensures that the GPT response is a valid JSON matching the desired JSON schema. Set structured_output
to true
and update _response_format
to define the expected JSON schema for structured output.
For example, if your prompt is:
"Translate this text into French, Italian, and German. Text to translate: " & [Text]
This is the GPT answer without enabling structured output:
Enable structured output:
Define the expected JSON schema for structured output:
This is the structured GPT answer:
Error Handling
If API returns a error, fxOpenAI function ensures that Power Query error message includes error description returned by the API.
For example, error message for invalid JSON schema for structured output:
Output
The function returns a record that includes the following fields:
Content – GPT response. Either text (structured output disabled) or record (structured output enabled)
FinishReason:
- “stop” means the API returned the full chat completion generated by the model without running into any limits.
- “length” means the conversation was too long for the context window.
- “content_filter” means the content was filtered due to policy violations.
FullResponse – GPT response with metadata (record), including usage data (number of input and output tokes).