Translate to Multiple Languages with Power Query - Part 3

[smozgur]

smozgur submitted a new Excel article:

Translating to Multiple Languages with Power Query - How to translate text to multiple languages.

In the previous article of this series, Power Query vs. Google Translation API - Part 2, we discussed how to make more effective Google Translation API calls by combining all rows as a whole text with a delimiter and send it to the web server in a single batch.

In this last article of the series, we are going to translate lyrics to multiple languages instead of hardcoding single target...

Read more about this Excel article...

 

[Tejasp]

@smozgur nice one its very interesting.

Is there any way to translate in English from another language? Other language is not fix ( it may vary from time to time)

Thanks,

 

[smozgur]

@Tejasp - it requires a couple changes. I will try to create a follow-up article to extend the logic form X language to Y languages.

However, to make it work with the current code and structure (just as a quick workaround):

1. Change the en parameter with the source language code that you'd like to translate from:
   

   "https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=" & LanguageCode & "&dt=t",

2. Change your Languages table to include the English - please not that I didn't use English as the Language name to be able to use the same code, otherwise it will be duplicate column name in Power Query and it will fail (remember, we are trying to tweak quickly, so we can make it work with the same code):
   

   Language	Code
   LangEng	en

3. Finally change the text in the Original table with the source language (keep the Original table header as English to be able to use the same code).

 

[smozgur]

smozgur updated Translate to Multiple Languages with Power Query - Part 3 with a new update entry:

Configurable source language

Bonus: Translating from any language instead of English.

@Tejasp asked if we can make the source language dynamic instead of translating from English only. Therefore, we can translate from another language to English as well.

We can change the M code a bit to make it work as shown below.

let
    fnTranslate = (Original as text...

Read the rest of this update entry...

 

[Tejasp]

@smozgur Thanks for this. but I have data something like below :

Language	Text
EN	Hello darkness, my old friend
FR	Je suis encore venu te parler
TR	Çünkü usulca sürünen bir vizyon
DE	Hinterließ seine Samen, während ich schlief
IT	E la visione che è stata piantata nel mio cervello

and I would like to get the output in below format.

Output

Hello darkness, my old friend

I've come to talk with you again

Because a vision softly creeping

Left its seeds while I was sleeping

And the vision that was planted in my brain

Do you think if this is possible?

 

[smozgur]

Do you think if this is possible?

It should be possible. Rows should be grouped by language and then translated in separate batches.
Like you number the items in this list (ID each row), group by language, and translate each separately by using the existing method, then put translation back to the main table by using the original IDs.
However, the current implementation can't be easily converted to this. I am not available to look at the moment, perhaps not for a while, but I'll try to post here when I have a chance to look at it.

 

[feboici]

Hi Smozgur, thanks for the sharing is it possible to share your file ? I can't find it available, sorry. Thanks a lot !

 

[smozgur]

Hi Smozgur, thanks for the sharing is it possible to share your file ? I can't find it available, sorry. Thanks a lot !

There is no sample file. You can simply create table(s) by copying & pasting the sample data, and create the query by using the provided code in the article.

Translate to Multiple Languages with Power Query - Part 3

In the previous article of this series, Power Query vs. Google Translation API - Part 2, we discussed how to make more effective Google Translation API calls by combining all rows as a whole text with a delimiter and send it to the web server in...

www.mrexcel.com

I suggest starting from the first part of the series:

Using Power Query in Excel to Translate Data Using the Google Translation API

When I started reading Jeffrey McManus' Database Access with Visual Basic book decades ago, I was very much aware that data was everything. I kept saying "You have a project only if you have a data set to manage". However, back in those days, you...

www.mrexcel.com

 

[feboici]

Hi Thx Smozgur ! Do you know what's happend if i have several raws in 1 cell ? i tested a solution using google translation tool (VBA code), it works well but it is translating only the first one
thx

 

[feboici]

I did the test and as expected it is not reading the return test :
Do you have an suggested solution ?

Thanks