• If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    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

Power Query vs. Google Translation API - Part 2

In the first article of this series, Using Power Query in Excel to Translate Data Using the Google Translation API, we discussed retrieving data from a web server by using a simple API call. In the article, we used the Google Translation API endpoint.

Although it seems like a good idea to translate text in each row separately, there are some disadvantages of using this method.
  • Every single API call means requesting data from the web server, just like opening a web page in a web browser. It works much slower if there are hundreds of rows to translate.
  • Google will eventually block the client IP to lower the access due to "Too many request attempts". Although it would not be a permanent ban, we will not be able to retrieve more data until the temporary ban is lifted.
So, would not it be perfect if we could make a single API call instead of hundreds of them?

In this article, we are going to see how to make "less cost and more effective" API calls by combining all rows as a whole text with a delimiter and send it to the web server in a single batch. Finally, we will process the returned text to create the translation table. As the source data, we will use the lyrics of Sound of Silence by Simon & Garfunkel to translate Turkish.
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

Let's start by looking at the Google Translate website. Google Translate lets us enter sentences as line items and returns the translation correspondingly.

Google Translate

Google Translate

This method provides more accurate translation since the Google Translate engine considers each line as a separate sentence to process, and it will also save us from using a custom separator in M code.

We can summarize the process as shown below.
  • Get the source data from the worksheet, preferably as a table (worksheet ListObject) item. Please note that we can use named ranges as a data source as well.
  • Create a List by referring the table column which includes English lyrics.
  • Combine the list items as a single text value by using a newline character as a separator.
  • Call Google Translate API with necessary parameters and convert the returned JSON string to a List.
  • Create the result table by combining Original and Translation items.
Create a new blank query and copy and paste the following M code in Power Query Advanced View. As a difference from the previous article, we will not use a helper function to do the translation job this time. So, we will be able to see the generated result at each step.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
    TextList = Source[English],

    NewLine = "#(cr)#(lf)",
    CombinedText = Text.Combine(TextList, NewLine),

    PostContent = "q=" & CombinedText,
    WebResult =
        Web.Contents(
            "https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&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, "")
    ),

    CombinedLists = List.Generate(
        () => [i = 0],
        each [i] < List.Count(TextList),
        each [i = [i] + 1],
        each [English=TextList{[i]}, Turkish=Translation{[i]}]
    ),

    ConvertedToTable = Table.FromList(
        CombinedLists,
        Splitter.SplitByNothing()
    ),
    Result = Table.ExpandRecordColumn(
        ConvertedToTable,
        "Column1",
        {"English", "Turkish"}
    )
in
    Result
Let's break down this code.
  • Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content]

    As usual, we get the source data into Power Query by using Excel.CurrentWorkbook function.

    Excel.CurrentWorkbook function

    Excel.CurrentWorkbook function

  • TextList = Source[English]

    Since we will combine all text lines as a single text instead of translating separately, we create a list from the table column by referencing the column with its name. We use square brackets to refer a table column by its name to form a List object.

    Refer a table column to form a List object

    Refer a table column to form a List object

  • NewLine = "#(cr)#(lf)"

    Newline separator value. It is good practice to assign variables for the values that will be used multiple times in code.

    Assign newline constant as variable

    Assign newline constant as variable

  • CombinedText = Text.Combine(TextList, NewLine)

    We use Text.Combine function to concatenate lyrics. Text.Combine function takes a list as the first parameter and returns the result of combining the list of text values by using the optional separator parameter which is provided as the second parameter. We use the NewLine constant variable as a separator.

    Text.Combine function to concatenate text

    Text.Combine function to concatenate text

  • PostContent = "q=" & CombinedText

    In the previous article, we used the GET request method to retrieve data from the web service that we added original text as a query string in the URL. However, since the combined original text is much longer than the individual line texts, this time we use the POST method to send it to the web service. See HTTP Request Methods for more information about GET and POST.

    We create the PostContent variable as a concatenated text consists of a field name (q) and combined text.

    HTTP POST request variable

    HTTP POST request variable

  • WebResult = Web.Contents(...)

    To make a POST request to a web service, we have to provide the necessary options as a record as the second parameter of Web.Contents function such as Headers and Content as used in this project.
    Web.Contents function's options parameter will be more vital when we start making API calls that require authentication in the future articles.
    Web.Contents function with options

    Web.Contents function with options

  • JsonValue = Json.Document(WebResult)

    Web.Contents function returns binary data, so it should be parsed according to the format it was generated. Google Translation API returns JSON string as we discussed in the previous article. Therefore, we use Json.Document function to parse the returned data.

    Parse JSON string by using Json.Document function

    Parse JSON string by using Json.Document function

  • Translation = List.Transform(...)

    Remember the first list item in JsonValue contains the translated text array. Google Translation API splits the posted text by using the newline as a separator, translates each item individually, and returns an array contains the translation for each item. However, the translated text also contains carriage return and line feed characters at the end of the text (look at the Google Translate screenshot).

    We use List.Transform function to replace these characters with an empty string. List.Transform function takes two parameters. The first parameter is the list object that will be transformed, and the second parameter is the function that we want to apply to each item in the list. We use Text.Replace function to replace the newline character with an empty string as the second parameter. You will notice that Text.Replace function is similar to the Replace function in VBA.

    Remove extra newline characters by using Text.Replace function

    Remove extra newline characters by using Text.Replace function

  • CombinedLists = List.Generate(...)

    We have the TextList list variable which contains original lyrics. We also extracted a list from the web service response, Translation. Both list variables contain the same number of items.
    Can hear the question, "Why don't we create a two-column list instead of a list with a record that contains two fields?". Because there is no "two columns List", but Table. So, we do the transformation by using the list, then we convert it to a table to load to the worksheet.
    As explained in Power Query List.Generate vs. VBA For...Next, we can create a loop by using List.Generate function in Power Query M language. Since the list objects contain the same number of items in our code, we loop through the list object items and create a record for each item which contains English and Turkish fields and their corresponding text values. Finally, we have a list that consists of records that we can convert to a table and expand those records as table columns.
    Although it is a good method to use in this project, List.Generate function loops through all list items, and it might not be efficient for larger data sets. An optional method to join two corresponding lists would be converting lists to tables, adding index columns to both tables, and then merging these tables by creating an inner join kind query on the index columns by using Table.Join function.
    Generate new list including corresponding Original and Translation texts

    Generate new list including corresponding Original and Translation texts

  • ConvertedToTable = Table.FromList(...)

    We create a table by using Table.FromList function from the list that we just created. The optional second parameter of Table.FromList function is the separator function that is applied to each item in the list and it is set as "comma" as default. However, our list items consist of a Record that is not supposed to be separated. Therefore, we use Splitter.SplitByNothing function.

    You can preview record contents by selecting a row in the table.

    Create a new table from the list of records

    Create a new table from the list of records

  • Result = Table.ExpandRecordColumn(...)

    Finally, we expand the Column1 column to spill the record fields as separate columns. We use Table.ExpandRecordColumn function to expand Record objects in a table column.

    Table.ExpandRecordColumnfunction takes four parameters.
    • table: Table identifier which contains the record column to expand.
    • column: Name of the column to expand.
    • fieldNames: The list which contains the name of fields to expand into columns in the table.
    • newColumnNames: The list which contains the new column names.

  • The last parameter is optional, and the actual field names are used as the column names if it is omitted. We omit the last parameter since the record field names are already the names that we would like to use as column names.
    During the table column expansion in M, we mostly have to provide new column names since the new column names cannot duplicate any column in a table. This might look like a problem when we are working with dynamic fields that we don't know the field names. However, M language has many other powerful functions to deal with this situation that we will see in future articles.
    Expand record column to create new columns from records

    Expand record column to create new columns from records
Finally, we load this query result to the worksheet, and we are done.
EnglishTurkish
Hello darkness, my old friendMerhaba karanlık, benim eski dostum
I've come to talk with you againSeninle tekrar konuşmaya geldim
Because a vision softly creepingÇünkü hafifçe sürünen bir vizyon
Left its seeds while I was sleepingBen uyurken tohumlarını bıraktı
And the vision that was planted in my brainVe beynime yerleştirilen vizyon
Still remainsHala aynı
Within the sound of silenceSessizliğin sesi içinde
In restless dreams I walked aloneHuzursuz rüyalarda yalnız yürüdüm
Narrow streets of cobblestoneArnavut kaldırımlı dar sokaklar
'Neath the halo of a street lampSokak lambasının halesinin altında
I turned my collar to the cold and dampYakamı soğuğa ve neme çevirdim
When my eyes were stabbed by the flash of a neon lightGözlerim neon bir ışığın parlamasıyla bıçaklandığında
That split the nightGeceyi bölen
And touched the sound of silenceVe sessizliğin sesine dokundu
And in the naked light I sawVe çıplak ışıkta gördüm
Ten thousand people, maybe moreOn bin insan, belki daha fazla
People talking without speakingKonuşmadan konuşan insanlar
People hearing without listeningDinlemeden duyan insanlar
People writing songs that voices never shareSeslerin asla paylaşmadığı şarkılar yazan insanlar
And no one daredVe kimse cesaret edemedi
Disturb the sound of silenceSessizliğin sesini boz
Fools said I, "You do not knowAptallar, "Bilmiyorsun
Silence like a cancer growsSessizlik tıpkı bir kanser gibi büyüyor
Hear my words that I might teach youSana öğretebileceğim sözlerimi duy
Take my arms that I might reach you"Kollarımı tut ki sana ulaşabilirim "
But my words like silent raindrops fellAma sessiz yağmur damlaları gibi sözlerim düştü
And echoed in the wells of silenceVe sessizliğin kuyularında yankılandı
Within the sound of silenceSessizliğin sesi içinde

Thanks for reading! In the final article of this series, Translate to Multiple Languages with Power Query - Part 3, we will translate lyrics to multiple languages instead of hardcoding single target language in code.
Excel Version
365, 2019, 2016, 2013
  • Like
Reactions: MARK858 and GraH
Author
smozgur
Views
147
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from smozgur

Some videos you may like

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top