Connecting Azure Deepseek API with Power BI Using Power Query

[jianfajun]

Introduction

Hello everyone! Recently, Deepseek has become a very popular topic, and Azure has also launched open-source versions of Deepseek R1 and V3 models. I've done some interesting tasks – I successfully connected Power BI with the Azure Deepseek API.

This connection is implemented through Power Query code as a custom function, without using any complex middleware. Calling it is extremely simple – you just invoke the custom function. We can leverage Deepseek AI's capabilities to read data from Power BI tables and return results. In this example, we receive online customer feedback, and we want to classify feedback into categories such as "Inquiry," "Complaint," "Refund" etc.

Azure DeepSeek API Configuration

I believe some of you may already have Azure accounts, so I'll just use Azure DeepSeek API models as an example. First, we need to configure Azure AI Foundry. Let me walk you through it step by step:

I recommend starting with the Azure AI Foundry (ai.azure.com) rather than the Azure portal. Here we need to create an AI resource. Click "Create Project," and in the popup box, select an existing AI Hub or create a new one.

After creating the project, we can enter the project interface. On the left, find "Model catalog," and you can find DeepSeek in the most visible place on the page. Currently, there are two models available: one is the chat model DeepSeek-V3, and the other is the reasoning model DeepSeek-R1.

After you enter the DeepSeek model page, just lick "Deploy". During the deploy process, there is a function "Content Filters." If you want to experience the original DeepSeek model, be sure to set the content filter to "None".

Then, we've completed the model deployment. Next, we need to find the key information on the model page to connect DeepSeek with Power BI. The essential information includes:

• API Endpoint URL
• API Key
• Model Name

Power Query Custom Function

Below is the Power Query custom function code for connecting to the Azure DeepSeek API. You should replace the baseUrl and apiKey with your own information. 

let
    /*
     * Azure DeepSeek API Connector for Power Query
     * ---------------------------------------
     * A function to interact with Azure DeepSeek's Large Language Model API.
     */
    fnDeepSeekChat = (
        // Required parameters
        systemContent as text,  // System instructions for AI behavior
        userContent as text,    // User's actual query or prompt
        
        // Optional parameters
        optional model as text,        // Model name to use
        optional maxTokens as number,  // Controls maximum response length
        optional temperature as number // Controls randomness (0-1), lower = more deterministic

    ) as any =>
    let
        // Azure API Configuration, replace with your own endpoint
        baseUrl = "https://<YOUR OWN URL>.services.ai.azure.com/models/chat/completions",
        apiVersion = "2024-05-01-preview",
        apiUrl = baseUrl & "?api-version=" & apiVersion,
        // API key should be defined externally as AzureDeepSeekAPIKey
        apiKey = "<YOUR OWN API KEY>",
        
        // Default settings
        DefaultModel = "DeepSeek-V3",
        DefaultMaxTokens = 300,      // Default token limit
        DefaultTemperature = 0.7,    // Balanced between creativity and determinism

        // Set final parameter values
        finalModel = if model <> null then model else DefaultModel,
        finalMaxTokens = if maxTokens <> null then maxTokens else DefaultMaxTokens,
        finalTemperature = if temperature <> null then temperature else DefaultTemperature,
        
        // Configure request headers - Azure uses api-key
        headers = [
            #"Content-Type" = "application/json",
            #"api-key" = apiKey
        ],
        
        // Build request body
        requestBody = Json.FromValue([
            model = finalModel,
            messages = {
                [role = "system", content = systemContent],
                [role = "user", content = userContent]
            },
            max_tokens = finalMaxTokens,
            temperature = finalTemperature,
            stream = false
        ]),
        
        // Execute API request with error handling
        // Adding 500ms delay to avoid potential rate limiting
        response = Function.InvokeAfter(
            () => try Web.Contents(apiUrl, [
                Headers = headers, 
                Content = requestBody,
                // Manual handling of error status codes for better diagnostics
                ManualStatusHandling = {400, 401, 403, 404, 429, 500}
            ]) otherwise null,
            #duration(0, 0, 0, 0.5)
        ),
        
        // Parse JSON response
        json = if response is null then null else try Json.Document(response) otherwise null,
        
        // Extract response text with error handling
        result = 
            if json <> null and Record.HasFields(json, "choices") and List.Count(json[choices]) > 0 then
                try json[choices]{0}[message][content] otherwise "Error: Invalid response format"
            else
                "Error: No valid response"
    in
        result
in
    fnDeepSeekChat

How to Use the Custom Function

Now we have this custom function. And how can we use it? I'll introduce two common methods: direct invoking and batch processing in tables.

Direct Invoking for Results: This method is similar to using the deepseek app or ChatGPT app directly. Simply input parameters into the custom function.

systemContent: "You are a customer service classification expert. Categorize the input text into exactly one of the following categories: 'Inquiry', 'Complaint', 'Refund', 'Support', 'Suggestion'"

userContent: "I was charged twice for my order. Please refund one of the payments."

You can also fill in the optional parameters. For instance, if you want a more rational result, set the temperature parameter lower. In this example, I've adjusted it to 0.1.

Batch Processing in Tables: A more powerful approach is applying the function to tables to process multiple records in a batch. This method is particularly suitable for classification and text labeling:

1. First, ensure you have a table containing text data (such as a "Customer Feedback Table")
2. Click "Add Column" → "Invoke Custom Function"

1. In the popup window, select the custom function
2. Set values for the function parameters:
   1. systemContent: Enter text such as "You are a customer service classification expert. Categorize the input text into exactly one of the following categories: 'Inquiry', 'Complaint', 'Refund', 'Support', 'Suggestion'"
   2. userContent: Instead of inputting text directly, choose a column from your data table
   3. Configure other parameters as needed
3. Click "OK", and Power Query will call the API for each row of data and create a new column.

Batch Processing Tips:

1. Performance Optimization: When batch processing, API calls can be time-consuming. It's better to test with a small amount of data first, and once you confirm the results meet your expectations, then process the complete dataset.
2. Cost Control: Remember that every time you refresh the report, the API will be called again, which incurs costs.