AI Power Query and M

M Language Function to Call Open AI API from Power Query

Andrzej Leszkiewicz

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).

Share the article