Sneak Peek: Translate Text in Power BI or Excel with Microsoft Translator Text API

Ever wanted to automatically translate text in Power BI or Excel?

Now you can! As part of Microsoft Cognitive Services, you can now use Microsoft Translator Text API inside Power BI or Power Query in Excel. To download the Power BI template file, or the Excel sample file, and try it before everyone else, subscribe to DataChant below.

In the past, I have demonstrated how to build Power BI and Excel reports that uses Microsoft Cognitive Services to analyze Sentiment or detect Key Phrases in text messages as shown in this example:

In the report above, we extract key phrases from the followers’ description on Twitter. All the logic is serverless, and is done inside Power BI and API calls to Microsoft Cognitive Services.

But if you need to analyze sentiment of unsupported languages?  You can now use Power BI and Microsoft Translator Text API to translate your messages to English and then pass the translated text to the Text Analytics API (e.g. Sentiment Analysis or Key Phrases) in Microsoft Cognitive Services.

Here is a screenshot of the Power BI Query Editor that include the advanced queries that performs the translation to Hello World from five different languages. These queries can be yours 🙂

Translate text in Power BI or Power Query in Excel

Instructions for DataChant Subscribers

Unlike the Text Analytics API which doesn’t require Azure subscription, to use my template, you should first have an account on Azure and follow steps 1-12 in this document.

Once you obtain the key (in step 12), you will need to copy it.

Azure Translator API

Download the Power BI Template Azure Translator API.pbit from the subscribers’ folder, and open it with Power BI Desktop.

Paste the Azure Translator API key in the TranslatorKey box, and click Load.

Paste the Azure Translator API key here
Paste the Azure Translator API key here

Alternatively, after susbscribing, you can download Azure Translator API.xlsx file and paste the key in the relevant cell:

Translate text in Excel

Subscribe To DataChant

Join our mailing list to receive exclusive content that was used in the preparation of this article.

Thank you for subscribing to DataChant. An email will be sent to you shortly with more details.

Note: The solution was written for learning purposes. Each text message is sent separately to Azure. To better utilize the service, you can use TranslateArray call instead of Translate (But it was beyond the scope of a sneak peek post).

Looking forward to your feedbacks. Did you like this template? Found any issues? Reply below to help me improving the template, before I publish the tutorial.

6 comments

    • Gil Raviv Post authorReply

      You can subscribe to DataChant. After you confirm your email address (check your spam folder for the confirmation email, if you didn’t get it in your inbox), you will receive a second email with the link to the subscribers’ folder.

    • Gil Raviv Post authorReply

      You probably try to feed the function FnTranslateText with a table instead of text. Did you perform any change from the original queries? Can you share it below?

  1. MarekReply

    Hello, Gil,
    I have experienced errors for function: “Query references other queries…please, rebuild this data combination”. I share this post only to point on setting of Privacy – make sure you select “Aways ignore privacy level settings”.
    Thank you for sharing!

Leave a Reply