• 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

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

Excel Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
When I started reading Jeffrey McManus' Database Access with Visual Basic book decades ago, I was very much aware that data was everything. I kept saying "You have a project only if you have a data set to manage". However, back in those days, you were mostly limited with the data included in the project that you were working on and you had to create that data source first. Perhaps it was the reason for "database programming" being underestimated back then.

Nowadays, unlimited data is flying all over the Cloud through web services provided by the owner of those data, and programming languages evolved to serve data to or read data from these web services easily on the Internet. An API (Application Programming Interface) is a software interface that allows two applications to interact with each other. Although APIs provide more than just exchanging data, in this article, I will try to demonstrate using APIs in Power Query to read data from a web service which returns data in JSON format.
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.

Google Translation API (free version) is the web service that we will use to translate between languages.
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.

Google Translation API (free version) can be accessed by using the following URL.
Rich (BB code):
https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=Good%20morning

This URL contains the following parameters:
  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.
This API endpoint returns a JSON file in array format which contains Turkish translation of "Good morning" text given in English. You can change the sl or tl parameters to translate to or from other languages. See the complete list of ISO language codes in the second column of Codes for the Representation of Names of Languages.
JSON:
[
    [
        [
            "Günaydın",
            "Good morning",
            null,
            null,
            1
        ]
    ]
    ,null,"en",null,null,null,null,[]
]

If we name this array as Source, then Source[0][0][0] array element contains the translated text. (Remember that Power Query starts numbering at zero, so Source[0][0][0] is saying the first item in the first list in the first field of the returned data.) To use Power Query for translating, you need some M code that will request this JSON file, parse its content, and load the data after some transformation to present the corresponding translation in tabular format.

Web.Contents is the M language function that we use to read an API endpoint by providing query parameters. Create a blank query in Power Query, switch to the advanced editor, and paste the following M code.
Power Query:
let
    Source = Web.Contents("https://translate.googleapis.com/translate_a/single?client=gtx&sl=en&tl=tr&dt=t&q=Good Morning")
in
    Source

As soon as we run the query, we will be notified to specify how we would like to connect to the web service unless you already defined permission for the Google Translation API endpoint. Click on the Edit Credentials button.
Specify how to connect

Specify how to connect

Select Anonymous access, and root level of Google Translation API as shown in the following image. Click Connect to continue.
Access Web content settings

Access Web content settings

When we run the query, API returns a file, translate.googleapis.com, which contains the JSON string that we discussed above.
Web.Contents function

Web.Contents function

We need to parse this JSON file to access the translated text. Json.Document is the function which is responsible of parsing the JSON content. Use Json.Document function as shown below.
Power Query:
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.

Json.Document function

Json.Document function

Json.Document returns a list which contains the array that we discussed previously. We also discussed that Source[0][0][0] is the translated text we want to get. So, let's set the Result variable to include this array value, but using curly braces as it is used to access list element items in M code.
Power Query:
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

Let's see how it works by analyzing the steps individually.
  • First, we retrieve the root element returned as Source.
    Step 1
  • Then the first item of this list. It is another list with one list item.
    Step 2
  • Once again, the first item in this list.
    Step 3
  • Notice the first item of this inner list. It is the translated text. Finally, we need to retrieve the first item in this list.
    Step 4
Since we know how to translate the given text now, let's create our source table which contains English sentences to translate, and name it as "Original".
English
This is a Power Query sample.
It is translating from English to another language
It has been written in M code.

Copy and paste the following code into a blank query. I intentionally included a custom function, fnTranslate(), to demonstrate basic encapsulation and reusability in M code. We don't have to do this in this project but it provides more readable code. Also, we can even create the custom function as another query and call it in many other queries.
Power Query:
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

Let's break down this code.
  • 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

    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

    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

    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.
Finally, we get the following result when we run this query.
Result

Result

Load query to the worksheet. Try adding some more text in English into the "Original" table, and refreshing the table. Finally, we have a working translator in Excel!
EnglishTurkish
This is a Power Query sample.Bu bir Power Query örneğidir.
It is translating from English to another languageİngilizceden başka bir dile çeviri yapıyor
It has been written in M code.M kodunda yazılmıştır.

Thanks for reading! In the second article of this series, Power Query vs. Google Translation API - Part 2, we discuss how to make a single API call instead of translating each line separately.
  • Like
Reactions: magoba and GraH
Author
smozgur
Views
3,838
First release
Last update
Rating
0.00 star(s) 0 ratings

More Excel articles from smozgur

This Week's Hot Topics

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
Top