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 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.
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.