Translate underlying data in data model dynamically to a different language

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