How to Translate Foreign Language Text with Alteryx

It’s relatively easy to translate text nowadays, with Google Translate just a few clicks and a copy and paste away, deciphering information from a language you have no skill in whatsoever has never been easier. But what if you needed to translate thousands of pieces of information within a dataset?

Well, one of our clients recently wished to do just this with one of their biggest surveys. Over 20,000 respondents from multiple countries around the world gave satisfaction scores on various performance areas relating to the business. They were also invited to leave a free text comment, which resulted in a large and rich data source for identifying problem areas. The problem is, these results were very localised and comments were left in more than 15 different languages. Copy and paste will no longer cut it.

Thankfully there are ways to access the technology that supports online translation services. APIs are available that handle the translation work, they just need to be adapted for complete ease of use within Alteryx. In solving this problem for the client, I created a macro that uses a Microsoft translation API that anyone can use following a few simple steps.

Solution: The Translator Macro

Here is the finished product, a macro that handles all the underlying API magic (sending data, getting authentication, tidying up the results) and requires minimal input from a user perspective to get a single field translated into a language of the user’s choosing.

Get the macro

You’ll need to download and save the macro from the Alteryx public gallery here (this will download a yxi file, which means Alteryx will install everything needed to use the macro).

Configuration

1. Simply enter your API Key (more on this below)
2. Choose the text field you need to be translated
3. Choose the language you need everything to be translated into

The Results

Once you’ve hit run and received that lovely ‘ding’ that tells you the workflow is complete, you’ll be able to see your original text, the translated text, and the language of your original text. Beautiful.

Now the text is translated you can start looking at doing other interesting things like getting sentiment analysis, or searching for the key themes/topics using the Cognitive Services Text Analytics tool.

Obtaining an API Key

Unfortunately, there is a little bit of groundwork required to get your API key, but this is nothing more than filling out a few forms to activate a Microsoft Azure account, and activating the specific Azure translation service you will use (via the Alteryx macro).

Follow the instructions at this Alteryx community blog post but, crucially, when choosing the API type, choose Translator Text API (not the Text Analytics API – this is for sentiment analysis).

Caution!

Microsoft has a free usage limit of 2 million characters per month, you can’t spend any money without explicitly selecting a paid tier of usage, but beware each time you run a workflow you will be using up your free credit, so it’s best to save the results on your first run to avoid rinsing through your allowance.

For more information on the use of the API see the following links.

https://azure.microsoft.com/en-us/services/cognitive-services/translator-text-api/

https://azure.microsoft.com/en-us/pricing/details/cognitive-services/translator-text-api/

https://docs.microsoft.com/en-us/azure/cognitive-services/translator/translator-info-overview

If you notice any bugs or have any feedback on this macro, please get in touch! Happy translating.

– PGB

Peter Gamble-Beresford

London, UK