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 🙂
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.
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
Alternatively, after susbscribing, you can download Azure Translator API.xlsx file and paste the key in the relevant cell:
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.
thanks for that – where can i download the Azure Translator API.pbit ?
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.
Thanks i got error “query. Expression.Error: We cannot apply operator & to types Text and Table.”
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?
OK your example works with my key. its ok, i am trying to understand the queries and the steps, thanks
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!
Hello,
thank you for this article.
I have the same question as Krisher Pini but I don’t understand your answer. I don’t know where I can download Azure Translator API.pbit.
Can you help me ?
You can subscribe to DataChant to gain the password, then go to this page to download the .pbit file: https://datachant.com/subscriber-corner/subscriber-resource-text-translator-api-power-bi-sample/
I am also getting an error:
“Formula.Firewall: Query ‘FnTranslateText’ references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.” I pasted in my TranslatorKey and immediately got this error. Any ideas what I am doing wrong?
Hi Alex,
Please go to File –> Options & Settings –> Options –> Current File –> Privacy, and set “Ignore the Privacy Levels…”. Then click Refresh.
Hi Gil,
How does this work if I want to replace text in my current Power (English) bi report to Spanish?
What steps do I need to be following?
I am kind of stuck. Any help/hint is much appreciated.
Thanks
I’m Getting an error on translated text. “Formula.Firewall: Query ‘FnTranslateText’ references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.” How will i be able to fix this?
You should set privacy to “Ignore privacy levels” in File -> Options & Settings -> Options -> Current File
Please make sure you follow the latest API here: https://datachant.com/2018/08/09/book-excerpt-translating-text-in-excel-and-power-bi-using-power-query/
Hi! I was able to set everything up as described, But now I would love to use it in combination with incremental refresh (not translating everything anytime I import the dataset to PBI service). I got a message that the web call is not supported by incremental refresh, so I looked for dataflow, but here I cannot get a connection / authentification. So and idea / advice how to proceed in this constellation?
I think that dynamic URLs in Dataflows are not supported yet, but it is worth rechecking…
Hi I cannot access https://docs.microsofttranslator.com/text-translate.html it says: 404 Web Site not found.
You may be seeing this error due to one of the reasons listed below :
Custom domain has not been configured inside Azure. See how to map an existing domain to resolve this.
Client cache is still pointing the domain to old IP address. Clear the cache by running the command ipconfig/flushdns. Checkout App Service Domain FAQ for more questions.
What do I need to do? I have run the command ipconfig/flushdns
Can you try https://docs.microsoft.com/en-us/azure/cognitive-services/translator/reference/v3-0-reference ?
Hi Gil, Thanks for the article.
I get error like ‘(404): Resource Not Found’ on ‘GetTranslatorAccessToken’ step.
I changed the URL with westeurope api endpoint but still doesn’t work. To be sure, I tested the url and parameters in microsoft’s test page ‘https://westeurope.dev.cognitive.microsoft.com/docs/services/57346a70b4769d2694911369/operations/57346edcb5816c23e4bf7421/console’ and everything looks fine but it doesn’t work in powerBI.
Any idea would be great.
Thanks
Can you send me a sample report with the queries you use? My email is gilra@datachant.com