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

JavaScript Object Notation (JSON) is an open-standard data interchange format designed to replace XML. It is a lightweight format for storing and transporting data.

Click to expand...

Google Translation API (free version) has some known limitations. This is not a bug but Google's design to lower the number of allowed requests per IP in a certain amount of time. So, try sending fewer requests basically by not refreshing the query often and keeping the translation list short. Please note this article's purpose is to demonstrate using Power Query to make simple API calls and work with retrieved data.

Google Cloud Translation API is Google's paid service for translation which provides controllable quotas and limits. However, the authentication method required by this API is not discussed in this article.

Click to expand...

https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=Good%20morning

1. sl is used for source language ISO code.
2. tl is used fr Target language ISO code.
3. q is the text to be translated.

[
    [
        [
            "Günaydın",
            "Good morning",
            null,
            null,
            1
        ]
    ]
    ,null,"en",null,null,null,null,[]
]

let
    Source = Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=Good Morning")
in
    Source

let
    Source = Json.Document(
        Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=Good Morning")
    )
in
    Source

Alternatively, you can right click on the downloaded file and select JSON to automatically apply Json.Document function.

Click to expand...

let
    Source = Json.Document(
        Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=Good Morning")
    ),
    Result = Source{0}{0}{0}
in
    Result

• First, we retrieve the root element returned as Source.
  
  
• Then the first item of this list. It is another list with one list item.
  
  
• Once again, the first item in this list.
  
  
• Notice the first item of this inner list. It is the translated text. Finally, we need to retrieve the first item in this list.
  
  

let
    fnTranslate =
        (original as text) as text =>
            let
                Source = Json.Document(
                    Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=" & original)
                ),
                Translation = Source{0}{0}{0}
            in
                Translation,

    Source = Excel.CurrentWorkbook(){[Name="Original"]}[Content],
    ChangeDataType = Table.TransformColumnTypes(Source,{{"English", type text}}),
    Result = Table.AddColumn(
        ChangeDataType,
        "Turkish",
        each fnTranslate([English]),
        type text
    )
in
    Result

• fnTranslate = (original as text) as text => let ... in ... custom function; We create a function which takes a text in English as the only parameter and does the translation as we did in the previous sample above. It still translates single text but it will be called for each row in the source table.
• Excel.CurrentWorkbook(){[Name="Original"]}[Content]: We get the source data into Power Query by using Excel.CurrentWorkbook function. Excel.CurrentWorkbook function returns a table including records consist of Excel ListObjects (worksheet tables), named ranges, and dynamic ranges in the current workbook with Content and Name fields.
  
  
  
  Excel.CurrentWorkbook function
  Each table row is a record in M language and each record can be selected by the row index which is starting from 0. So, myTable{0} returns the first row in a table called myTable. However, rows can be also referenced by their field values in case there is only one matched record. Since Excel table names are unique in a workbook, we can simply use this method to get the table by its name into Power Query by simply passing [Name="Original"] record instead of row index as shown below.
  
  
  
  Retrieve Table / ListObject by name
  Each field in a record can be referenced by the field name in square brackets after the record identifier. So, using the sample table in the previous step, myTable{0}[field1] returns field1 field value of the first row from myTable table. Finally, we get the Content field value as a table by referencing with the field name.
  
  
  
  Expand table in Power Query
• TransformColumnTypes(Source,{{"English", type text}}): M language is a strict language about data types, that's why the amazing Power Query user interface always changes the row types appropriately before making any processing in records by using functions which require parameters in certain data types. Since our custom function also requires a text data type, we use Table.TransformColumnTypes function to define the field type as text.
• Table.AddColumn(...): This is the step where we use Table.AddColumn function to create a new column to return translated text. Table.AddColumn function takes four parameters:
  • table: Source table to add a new column.
  • newColumnName: New column's name.
  • columnGenerator: A function to return new column values by processing each record in the table. We run fnTranslate() function with a text parameter to call Google Translation API for each record - fnTranslate([English]). [English] is the English field (column) value for the selected row.
  • columnType: This parameter defines the type of the new column. Since the new column is supposed to return text values, we used type text. We can also use the equivalent value, Text.Type. For more information, see M Language types.