Forum Discussion

ELIU's avatar
ELIU
Icon for Helper II rankHelper II
1 year ago
Solved

Translate underlying data in data model dynamically to a different language

Hi,

 

I have done some research and still couldn't figure out what would be the best way to dynamically translate the actual underlying data to a different language than English. For example, I have 10K rows and +20 columns in a table and need to translate every row into a different language today. Next day there are 100 new rows coming in to the PBIX file after the data source is refreshed. The 100 new rows of data also needs to be translated automatically. 

 

Any hints? Appreciate it!

Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi ELIU ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    1. Using a Translation API (Microsoft Translator)

    Step-by-Step Workflow:

    Step 1: Prepare Your Azure Translator API. Create an Azure account if you don't have one. Set up a Translator resource under Azure Cognitive Services. Obtain the API key and endpoint URL.

    Step 2: Use Power Query in Power BI to Call the API. In Power BI Desktop, go to Transform Data ->open Power Query Editor.

    Add a new column (or modify an existing one) that makes a web request to the Translator API using M code.

    Sample M Code:

    let
    sourceText = [YourColumnToTranslate],
    apiKey = "YOUR_API_KEY_HERE",
    endpoint = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&to=fr",
    body = "[{""Text"":""" & sourceText & """}]",
    response = Json.Document(Web.Contents(endpoint,
    [
    Headers = [
    #"Ocp-Apim-Subscription-Key" = apiKey,
    #"Content-Type" = "application/json"
    ],
    Content = Text.ToBinary(body)
    ])),
    translatedText = response{0}[translations]{0}[text]
    in
    translatedText


    Step 3: Refresh Logic

    Every time the dataset is refreshed, Power Query will send new (or changed) rows to the API for translation. You can use conditional logic to avoid re-translating already translated rows.

    2. Use Power Query and Custom Connector Together

    A custom connector is a piece of M code that encapsulates external API access logic. You use this to simplify or securely manage API calls from Power Query.

    Why Use a Custom Connector?
    Better security (store secrets like API keys safely). Reusability across reports. Cleaner Power Query code (use connector as a function). Support for OAuth2 or API key authorization flows.

    Please follow below steps for Custom Connector

    Step 1: Create a custom connector using M and the Power Query SDK for Visual Studio.
    Step 2: Code your connector to accept a text input and return the translated result using the Translator API.
    Step 3: Deploy the connector and use it in Power BI as a data source.

    Example: M Function You Could Wrap in a Connector

    (sourceText as text, targetLang as text) as text =>
    let
    body = "[{""Text"":""" & sourceText & """}]",
    response = Json.Document(Web.Contents("https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&to=" & targetLang,
    [
    Headers = [
    #"Ocp-Apim-Subscription-Key" = "your_key",
    #"Content-Type" = "application/json"
    ],
    Content = Text.ToBinary(body)
    ])),
    translated = response{0}[translations]{0}[text]
    in
    translated

     

     

    If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

    Thank you

4 Replies