- Excel Version
- 365
- 2019
- 2016
- 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.
We are going to use the same lyrics that we used in the previous article, the table named "Original".
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,
Nothing too much different until List.Generate function. So, let's start analyzing the code starting from that line.
We know that List.Generate function takes four parameters, initial function, condition function, next function, and finally selector function.
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.
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.
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.
| Language | Code |
| Turkish | tr |
| Italian | it |
| German | de |
| French | fr |
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
ResultNothing 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.
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.
| English | Turkish | Italian | German | French |
| Hello darkness, my old friend | Merhaba karanlık, benim eski dostum | Ciao oscurità mia vecchia amica | Hallo Dunkelheit mein alter Freund | Bonjour ténèbres, vieil ami |
| I've come to talk with you again | Seninle tekrar konuşmaya geldim | Sono venuto a parlare di nuovo con te | Ich bin gekommen, um wieder mit dir zu reden | Je suis venu te parler à nouveau |
| Because a vision softly creeping | Çünkü hafifçe sürünen bir vizyon | Perché una visione che striscia dolcemente | Weil eine Vision leise kriecht | Parce qu'une vision qui rampe doucement |
| Left its seeds while I was sleeping | Ben uyurken tohumlarını bıraktı | Ha lasciato i suoi semi mentre dormivo | Hat seine Samen verlassen, während ich geschlafen habe | A laissé ses graines pendant que je dormais |
| And the vision that was planted in my brain | Ve beynime yerleştirilen vizyon | E la visione che è stata piantata nel mio cervello | Und die Vision, die in mein Gehirn gepflanzt wurde | Et la vision qui a été plantée dans mon cerveau |
| Still remains | Hala aynı | Rimane ancora | Verbleibt noch | Reste encore |
| Within the sound of silence | Sessizliğin sesi içinde | Nel suono del silenzio | Im Klang der Stille | Dans le son du silence |
| In restless dreams I walked alone | Huzursuz rüyalarda yalnız yürüdüm | In sogni inquieti camminavo da solo | In unruhigen Träumen ging ich alleine | Dans des rêves agités, j'ai marché seul |
| Narrow streets of cobblestone | Arnavut kaldırımlı dar sokaklar | Strade strette di ciottoli | Enge Straßen aus Kopfsteinpflaster | Ruelles pavées |
| 'Neath the halo of a street lamp | Sokak lambasının halesinin altında | 'Sotto l'alone di un lampione | 'Unter dem Heiligenschein einer Straßenlaterne | Sous le halo d'un réverbère |
| I turned my collar to the cold and damp | Yakamı soğuğa ve neme çevirdim | Ho girato il colletto al freddo e all'umidità | Ich drehte meinen Kragen zu kalt und feucht | J'ai tourné mon col vers le froid et l'humidité |
| When my eyes were stabbed by the flash of a neon light | Gözlerim neon bir ışığın parlamasıyla bıçaklandığında | Quando i miei occhi furono colpiti dal lampo di una luce al neon | Als meine Augen vom Blitz eines Neonlichts erstochen wurden | Quand mes yeux ont été poignardés par le flash d'un néon |
| That split the night | Geceyi bölen | Che ha diviso la notte | Das hat die Nacht geteilt | Qui a divisé la nuit |
| And touched the sound of silence | Ve sessizliğin sesine dokundu | E ha toccato il suono del silenzio | Und berührte den Klang der Stille | Et touché le son du silence |
| And in the naked light I saw | Ve çıplak ışıkta gördüm | E nella nuda luce ho visto | Und im nackten Licht sah ich | Et dans la lumière nue j'ai vu |
| Ten thousand people, maybe more | On bin insan, belki daha fazla | Diecimila persone, forse di più | Zehntausend Menschen, vielleicht mehr | Dix mille personnes, peut-être plus |
| People talking without speaking | Konuşmadan konuşan insanlar | Persone che parlano senza parlare | Leute reden ohne zu sprechen | Les gens parlent sans parler |
| People hearing without listening | Dinlemeden duyan insanlar | Le persone ascoltano senza ascoltare | Menschen hören ohne zuzuhören | Les gens entendent sans écouter |
| People writing songs that voices never share | Seslerin asla paylaşmadığı şarkılar yazan insanlar | Persone che scrivono canzoni che le voci non condividono mai | Leute, die Songs schreiben, die Stimmen niemals teilen | Des gens qui écrivent des chansons que les voix ne partagent jamais |
| And no one dared | Ve kimse cesaret edemedi | E nessuno osava | Und niemand wagte es | Et personne n'a osé |
| Disturb the sound of silence | Sessizliğin sesini boz | Disturbare il suono del silenzio | Stören Sie den Klang der Stille | Perturber le son du silence |
| Fools said I, "You do not know | Aptallar dedim ki, "Bilmiyorsun | Gli sciocchi mi hanno detto: "Non lo sai | Narren sagten ich: "Du weißt es nicht | Les imbéciles ont dit que je ne sais pas |
| Silence like a cancer grows | Sessizlik tıpkı bir kanser gibi büyüyor | Il silenzio cresce come un cancro | Die Stille wie ein Krebs wächst | Le silence comme un cancer grandit |
| Hear my words that I might teach you | Sana öğretebileceğim sözlerimi duy | Ascolta le mie parole che potrei insegnarti | Höre meine Worte, damit ich dich lehren kann | Entends 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 fell | Ama sessiz yağmur damlaları gibi sözlerim düştü | Ma le mie parole caddero come silenziose gocce di pioggia | Aber meine Worte wie stille Regentropfen fielen | Mais mes mots comme des gouttes de pluie silencieuses sont tombés |
| And echoed in the wells of silence | Ve sessizliğin kuyularında yankılandı | E riecheggiava nei pozzi del silenzio | Und hallte in den Brunnen der Stille wider | Et résonnait dans les puits du silence |
| Within the sound of silence | Sessizliğin sesi içinde | Nel suono del silenzio | Im Klang der Stille | Dans 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.
