• If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
  • Excel articles and downloadable files provided in the articles have not been reviewed by MrExcel Publishing. Please apply the provided methods / codes and open the files at your own risk. If you have any questions regarding an article, please use the Article Discussion section.
smozgur

Translate to Multiple Languages with Power Query - Part 3

Excel Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
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 language in code. Therefore we have an additional data table called "Languages" to store language names and corresponding language codes.
LanguageCode
Turkishtr
Italianit
Germande
Frenchfr

We are going to use the same lyrics that we used in the previous article, the table named "Original".
English
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
Still remains
Within the sound of silence
In restless dreams I walked alone
Narrow streets of cobblestone
'Neath the halo of a street lamp
I turned my collar to the cold and damp
When my eyes were stabbed by the flash of a neon light
That split the night
And touched the sound of silence
And in the naked light I saw
Ten thousand people, maybe more
People talking without speaking
People hearing without listening
People writing songs that voices never share
And no one dared
Disturb the sound of silence
Fools said I, "You do not know
Silence like a cancer grows
Hear my words that I might teach you
Take my arms that I might reach you"
But my words like silent raindrops fell
And echoed in the wells of silence
Within the sound of silence

Create a new blank query and copy and paste the following M code in Power Query Advanced View. Since this project makes multiple calls to the end-point, we can use a helper function, fnTranslate, that takes original text and language code as parameters and returns the translated text as a list.
Power Query:
let
    fnTranslate = (Original as text, LanguageCode as text) as list =>
        let
            PostContent = "q=" & Original,
            WebResult = Web.Contents(
                "https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=" & LanguageCode & "&dt=t",
                [
                    Headers= [#"Content-type"="application/x-www-form-urlencoded"],
                    Content=Text.ToBinary(PostContent)
                ]
            ),

            JsonValue = Json.Document(WebResult),

            Translation = List.Transform(
                JsonValue{0},
                each Text.Replace(_{0}, NewLine, "")
            )
        in
            Translation,

    Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
    Languages = Excel.CurrentWorkbook(){[Name="Languages"]}[Content],

    English = Source[English],
    NewLine = "#(cr)#(lf)",
    CombinedText = Text.Combine(English, NewLine),

    Translation = List.Generate(
        () => [i = 0],
        each [i] < List.Count(Languages[Code]),
        each [i = [i] + 1],
        each fnTranslate(CombinedText, Languages[Code]{[i]})
    ),

    Result = Table.FromColumns(
        List.InsertRange(Translation, 0, {English}),
        List.InsertRange(Languages[Language], 0, {"English"})
    )
in
    Result

Nothing too much different until List.Generate function. So, let's start analyzing the code starting from that line.
Power Query:
Translation = List.Generate(
        () => [i = 0],
        each [i] < List.Count(Languages[Code]),
        each [i = [i] + 1],
        each fnTranslate(CombinedText, Languages[Code]{[i]})
)

We know that List.Generate function takes four parameters, initial function, condition function, next function, and finally selector function.
  • We start creating a record field called i, which is the control value (counter) of the loop.
  • Check the control value at each step as we should do the translation for the languages in the source table.
  • Then increment the current control value and reassign i record field with the incremented value.
  • And finally, make the function call to translate the CombinedText by using the language parameter retrieved from the language source table by using the current control value as the row index.
This creates a list that contains nested lists consist of translated texts returned by the helper function.

List.Generate function

List.Generate function

See Power Query List.Generate vs. VBA For...Next for more information about creating a loop by using List.Generate function.
And finally Table.FromColumns function. Table.FromColumns function generates a table from a list consists of nested lists as columns that form a table together. Since each list items in the translation lists are in the same order, we can use Table.FromColumns function to put these lists together to create the result table.
Power Query:
Result = Table.FromColumns(
    List.InsertRange(Translation, 0, {English}),
    List.InsertRange(Languages[Language], 0, {"English"})
)

The first parameter of the function is the list contains the list columns to be joined. Additionally, we want the original English text to be placed in the first column. Therefore, we use List.InsertRange function to insert the English list column as the first column by providing 0 value as the index parameter as shown in the code. Notice we should provide the list column also as a list item, therefore we use curly braces to create a list contains the nested list item.

Similarly, as the second parameter of Table.FromColumns function, we provide the column names of the final table. And we are done.
EnglishTurkishItalianGermanFrench
Hello darkness, my old friendMerhaba karanlık, benim eski dostumCiao oscurità mia vecchia amicaHallo Dunkelheit mein alter FreundBonjour ténèbres, vieil ami
I've come to talk with you againSeninle tekrar konuşmaya geldimSono venuto a parlare di nuovo con teIch bin gekommen, um wieder mit dir zu redenJe suis venu te parler à nouveau
Because a vision softly creepingÇünkü hafifçe sürünen bir vizyonPerché una visione che striscia dolcementeWeil eine Vision leise kriechtParce qu'une vision qui rampe doucement
Left its seeds while I was sleepingBen uyurken tohumlarını bıraktıHa lasciato i suoi semi mentre dormivoHat seine Samen verlassen, während ich geschlafen habeA laissé ses graines pendant que je dormais
And the vision that was planted in my brainVe beynime yerleştirilen vizyonE la visione che è stata piantata nel mio cervelloUnd die Vision, die in mein Gehirn gepflanzt wurdeEt la vision qui a été plantée dans mon cerveau
Still remainsHala aynıRimane ancoraVerbleibt nochReste encore
Within the sound of silenceSessizliğin sesi içindeNel suono del silenzioIm Klang der StilleDans le son du silence
In restless dreams I walked aloneHuzursuz rüyalarda yalnız yürüdümIn sogni inquieti camminavo da soloIn unruhigen Träumen ging ich alleineDans des rêves agités, j'ai marché seul
Narrow streets of cobblestoneArnavut kaldırımlı dar sokaklarStrade strette di ciottoliEnge Straßen aus KopfsteinpflasterRuelles pavées
'Neath the halo of a street lampSokak lambasının halesinin altında'Sotto l'alone di un lampione'Unter dem Heiligenschein einer StraßenlaterneSous le halo d'un réverbère
I turned my collar to the cold and dampYakamı soğuğa ve neme çevirdimHo girato il colletto al freddo e all'umiditàIch drehte meinen Kragen zu kalt und feuchtJ'ai tourné mon col vers le froid et l'humidité
When my eyes were stabbed by the flash of a neon lightGözlerim neon bir ışığın parlamasıyla bıçaklandığındaQuando i miei occhi furono colpiti dal lampo di una luce al neonAls meine Augen vom Blitz eines Neonlichts erstochen wurdenQuand mes yeux ont été poignardés par le flash d'un néon
That split the nightGeceyi bölenChe ha diviso la notteDas hat die Nacht geteiltQui a divisé la nuit
And touched the sound of silenceVe sessizliğin sesine dokunduE ha toccato il suono del silenzioUnd berührte den Klang der StilleEt touché le son du silence
And in the naked light I sawVe çıplak ışıkta gördümE nella nuda luce ho vistoUnd im nackten Licht sah ichEt dans la lumière nue j'ai vu
Ten thousand people, maybe moreOn bin insan, belki daha fazlaDiecimila persone, forse di piùZehntausend Menschen, vielleicht mehrDix mille personnes, peut-être plus
People talking without speakingKonuşmadan konuşan insanlarPersone che parlano senza parlareLeute reden ohne zu sprechenLes gens parlent sans parler
People hearing without listeningDinlemeden duyan insanlarLe persone ascoltano senza ascoltareMenschen hören ohne zuzuhörenLes gens entendent sans écouter
People writing songs that voices never shareSeslerin asla paylaşmadığı şarkılar yazan insanlarPersone che scrivono canzoni che le voci non condividono maiLeute, die Songs schreiben, die Stimmen niemals teilenDes gens qui écrivent des chansons que les voix ne partagent jamais
And no one daredVe kimse cesaret edemediE nessuno osavaUnd niemand wagte esEt personne n'a osé
Disturb the sound of silenceSessizliğin sesini bozDisturbare il suono del silenzioStören Sie den Klang der StillePerturber le son du silence
Fools said I, "You do not knowAptallar dedim ki, "BilmiyorsunGli sciocchi mi hanno detto: "Non lo saiNarren sagten ich: "Du weißt es nichtLes imbéciles ont dit que je ne sais pas
Silence like a cancer growsSessizlik tıpkı bir kanser gibi büyüyorIl silenzio cresce come un cancroDie Stille wie ein Krebs wächstLe silence comme un cancer grandit
Hear my words that I might teach youSana öğretebileceğim sözlerimi duyAscolta le mie parole che potrei insegnartiHöre meine Worte, damit ich dich lehren kannEntends mes paroles que je pourrais t'apprendre
Take my arms that I might reach you"Kollarımı tut ki sana ulaşabilirim "Prendi le mie braccia per raggiungerti "Nimm meine Arme, damit ich dich erreichen kann. "Prends mes bras pour que je puisse t'atteindre "
But my words like silent raindrops fellAma sessiz yağmur damlaları gibi sözlerim düştüMa le mie parole caddero come silenziose gocce di pioggiaAber meine Worte wie stille Regentropfen fielenMais mes mots comme des gouttes de pluie silencieuses sont tombés
And echoed in the wells of silenceVe sessizliğin kuyularında yankılandıE riecheggiava nei pozzi del silenzioUnd hallte in den Brunnen der Stille widerEt résonnait dans les puits du silence
Within the sound of silenceSessizliğin sesi içindeNel suono del silenzioIm Klang der StilleDans le son du silence

Thank you for following this series! In this series, I tried to show how I would use the Google Translate API (free version) in Power Query. There are many different ways, and surely better methods to get the same result in Power Query. I am learning something new about Power Query every day, and there are so many to learn in the M language!

Although this series is finished, I am hoping to start another one discussing more API access which will focus on authorization, pagination, and also many other powerful M functions.
Author
smozgur
Views
7,118
First release
Last update
Rating
5.00 star(s) 1 ratings

More Excel articles from smozgur

Latest updates

  1. Configurable source language

    Bonus: Translating from any language instead of English. @Tejasp asked if we can make the...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top