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.

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




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.


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
  
  
  
• 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
  
  
  
• 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
  
  
  
• 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
  
  
  
• 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
  
  
  
• 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.

  Click to expand...

  
  
  
  
  
  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
  
  
  
• 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
  
  
  
• 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.

  Click to expand...

  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.

  Click to expand...

  
  
  
  
  
  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
  
  
  
• 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.

  Click to expand...

  
  
  
  
  
  Expand record column to create new columns from records

Finally, we load this query result to the worksheet, and we are done.

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.