Book Excerpt: Translating Text in Excel and Power BI using Power Query

I am excited to share an excerpt from the draft manuscript of my book. In this excerpt, which is part of chapter 12, “Advanced Text Analytics: Extracting Meaning,” you will learn how to translate text in your Excel and Power BI reports using Microsoft Cognitive Services’ Translator Text API and Power Query. Pre-order the book here.

This blog post was excerpted from the draft manuscript of Collect, Transform and Combine Data using Power Query in Excel and Power BI (Pearson Education, 2018). Original text was modified to adapt to the blog format.

---

Text Translation in Excel and Power BI

Accomplishing a computer-based language translation or sentiment analysis task does not require that you master a lifetime of studies and practice in artificial intelligence or computer science. Not very long ago, if you want to implement such methods and apply them in your reports, you needed large teams of software developers and data scientists. Until very recently, due to the computational and implementation complexities, very few organizations or businesses had reporting solutions that could utilize the power of artificial intelligence to extract meaning from textual feeds.

Fortunately, in this new era, artificial intelligence has become a commodity. You can now utilize cloud-based services to understand language and extract meaning. Microsoft’s Azure-based Cognitive Services allows you to integrate artificial intelligence in your business workflows. It offers services in five human cognition domains: vision, speech, language, knowledge, and search. With simple interfaces (web APIs), you can integrate some of these services into your reports in order to analyze your textual feeds.

See Also Azure Cognitive Services is available here.

In this blog post, you will learn how to create Excel and Power BI queries that can process textual feeds and apply language translation using Azure Cognitive Services’ Translator Text API.

Deploying the Translator Text API

Follow these steps to deploy the Microsoft Translator Text API on Azure and begin using it in your Excel workbook or Power BI report:

Note: Because Microsoft is constantly adding new features, the following instructions may not be fully accurate by the time you read this chapter. If that is the case, follow the instructions here.

1. For this exercise you will need an Azure account. Sign in to your Azure account here.
2. To subscribe to the Translator Text API, while signed in to Azure, go here.
3. Follow these steps to create a resource on Azure for the Translator Text API:
   • In the Name box, enter Translation-Resource.
   • Select your Azure subscription.
   • Select the pricing tier. You can use the free tier for textual feeds that are below 2 million characters. Read more about pricing here.
   • Select a new resource group or select an existing one. Resources groups help you combine and manage multiple resources on Azure. If this is your first time using Azure, you can create a new resource group, and name it Translation-Res-Group.
   • Read the notice and confirm that you have read it.
   • Select Pin to Dashboard.
   • Click Create.
4. Click Dashboard in the left pane of the Azure portal and select the Translation-Resource tile. In the Translation-Resource page that opens, click Keys in the left pane, under Resource Management.
5. As shown in the next figure, you can now obtain a copy of KEY 1 or KEY 2 (which you will use later in this chapter, in Exercise 12-1). You do not need to save the key as an interim step outside the report. At any time you can log in to the Azure portal and repeat step 4 to access this page.

Note: Ensure that the keys are not shared with anyone outside your team. You will learn about the security aspects of API keys in Power Query later in this chapter.

The Translator Text API Reference (v3.0)

While the Translator Text API reference is rich, with a wide variety of capabilities, this chapter focuses on the most basic method for translating a single message. This method is the Translate call of version 3.0 of the API (currently the latest version). For the full list of the API calls, go here.

The Translate call translates a string from one language to another. The request is passed as a web request with the request URI https://api.cognitive.microsofttranslator.com/translate, followed by the API version number as a value of the api-version parameter and a two-digit language code as a value of the to parameter for the target language you want to translate to. Here is an example for a request URI that translates text to English, using the currently 3.0 API version:

https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&to=en

If you wish to translate the text to a specific language, you can find which languages are supported here, and apply the two-digit code as a value for the to parameter. For example, the following URI translates Hola Mundo to Dutch:

https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&to=nl

To get the translation result, you need to be authorized by the service, and you get authorized by passing the API key in the call. In Exercise 12-1 you will learn how to pass the API key in the header Ocp-Apim-Subscription-Key. You will also pass the relevant text that you wish to translate. In version 3.0 of the API, the text should be sent in a JSON format in the body of the request:

[
    {"Text":"Hola Mundo"}
]

Exercise 12-1: Simple Translation

In this exercise you will implement the simple translation of a single textual message, Hola Mundo, by using Translator Text API version 3.0.

Part 1: for the Request Body

In the first part of the exercise, you will learn how to create the JSON content with the message you wish to translate, as needed in the request body of the Translate API call.

1. Open a blank Excel workbook or a new Power BI Report:
2. In Excel: On the Data tab, in the Get & Transform Data section, select From Other Sources and then select Blank Query.
3. In Power BI Desktop: Expand the Get Data drop-down and select Blank Query.
4. In the formula bar, enter the following a record of the text for translation:

= [Text = "Hola Mundo"]

This M formula, when translated to JSON, will be converted to the following format:

{"Text" = "Hola Mundo"}

5. Convert the record into JSON by wrapping what’s in the formula bar with the Json.FromValue function:

= Json.FromValue([Text = "Hola Mundo"])

In the Preview pane you now see a CSV icon. Right-click that icon and select Text. You can now see that the first row in the new table contains the desired format:

{"Text" = "Hola Mundo"}

6. It’s time to create the entire JSON block, including the wrapping brackets. In Applied Steps, select the Source step. Now, in the formula bar, modify the formula by wrapping the record in curly brackets, as follows:

= Json.FromValue( { [Text = "Hola Mundo"] } )

By adding the curly brackets, you have entered the M record into a list. Now select the last step in Applied Steps, Imported Text, to see how the modification will impact the JSON format. The first row in the table will have exactly the same format that you need for the JSON content:

[{"Text":"Hola Mundo"}]

Tip M and JSON have opposite definitions for records and lists. In M, brackets are used to define a record. In JSON, brackets define a list. In M, curly brackets are used to define a list, and in JSON they are used to define a record. This knowledge will help you craft the relevant JSON formats in M as you work on many .

7. Now that you know how to craft the JSON content with the translated text, delete Query1. You will soon use the last FromValue as the request body of another M expression.

Part 2: Building the API Key Parameter

It’s time to create a parameter for the API key, which will serve you later inside the API call. Continue Exercise 12-1 with the following steps:

8. Log in to the Azure portal and repeat steps 4–5 from the section, “Deploying the Translator Text API,” to copy one of the two API keys of the Translator Text API.
9. To create a parameter for the Translator Text API key that you copied in step 6, follow these steps:
   • On the Home tab of the Power Query Editor, expand the Manage Parameters drop-down and select New Parameter.
   • When the Parameters dialog box opens, in the Name box, enter APIKey; then, in the Current Value box, paste the API key from step 8. Then click OK to close the dialog box.

Part 3: Creating the Web Request

In this portion of the exercise, you will create the main web request call and retrieve the translated message from the response. Follow these steps to continue Exercise 12-1:

10. Create a new blank query and rename the query Translated Message. With the Translated Message query selected in the Queries pane, select Advanced Editor on the Home tab and enter the following M expression:

let
    Source = Web.Contents(
        "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&to=en",[
            Headers = [
                #"Ocp-Apim-Subscription-Key" = APIKey,
                #"Content-Type"="application/json"
            ],
            Content = Json.FromValue({[Text = "Hola Mundo"]})
        ]
    )
in
    Source

11. Let’s look closely at this expression. The main function that is used here is Contents, which can also be created when you select the Web connector through Get Data.

Note: Typically, when you want to import data from the web, you are not required to write the M expression from scratch, as you do in this exercise. You can select From Web in Get Data in Excel, or Web in Get Data in Power BI and use the From Web dialog box to provide the URL. You can even provide the HTTP headers in the f the From Web dialog box. However, when you are required to pass the request body, as in the case of the Translator Text API, you need to create the Web.Contents M expression.

Web.Contents contains two main elements, the URL, which is passed as the first argument, and the Options argument, which is passed as a second argument and is used as a container for all the other elements you can use to craft a web request, such as Headers and Content. In the M expression, you can see that Headers is a record of key/value pairs. Each includes the relevant header that is required for the API call.

The Headers record includes the Ocp-Apim-Subscription-Key header with the parameter APIKey that you created in step 9. Because the header name contains a dash, which is a special character, you must wrap it with #”…”. Content-Type is another required header, and it is represented as the second key in the Headers.

Following the Headers record, you can find the Content element, with the Value that you created in step 6.

Note: You can also create Web.Contents by using more options, including RelativePath and Query, and you can move more elements from the URL into the Options argument. Here is the relevant M expression, with the new elements in bold. You can see in this new expression that the URL contains only the domain name:

let
    Source = Web.Contents(
        "https://api.cognitive.microsofttranslator.com", [
            RelativePath = "translate",
            Query = [
                #"api-version" = "3.0",
                to = "en"
            ],
            Headers  = [
                #"Ocp-Apim-Subscription-Key" = APIKey,
                #"Content-Type" = "application/json"
            ],
            Content = Json.FromValue({[Text = "Hola Mundo"]})
        ]
    )
in
    Source

Tip: There are several advantages to using RelativePath and Query in Web.Contents. The RelativePath field in many cases improves the chances that your advanced queries will support schedule refresh on the Power BI service. The use of Query enables the M engine to automatically apply URI encoding on the query values and avoid unexpected handling of the text by the service.

12. When a notification bar appears in the Preview pane, select Edit Credentials. The Web Access dialog box opens. Ensure that Anonymous is selected and click Connect.
13. In the Preview pane, look for the JSON icon with the text cognitive.microsofttranslator.com. Right-click this icon and select JSON. The JSON response includes a list with a single record.
14. Because you passed only a single message, select the record by clicking the hyperlink.
15. In the Preview pane you now see a record of detectedLanguages and a list of Translations. If you click on the white space of the detectedLanguages cell, you can see the result record, with language=es and score=1. This means the service identified the source language as Spanish, with 100% certainty. Because your focus is to get the translated text in English, you can ignore this record and drill down to the translated text.
16. Click the List hyperlink in the translations,
17. In the Preview pane, which now shows a list with a single record, click the Record You now see a new record with text and to fields. The translated text is located as the value of text.
18. Right-click the cell with the value Hello World and select Drill Down from the shortcut menu.
19. Load the Translated Message query into your workbook or Power BI report, and save it.

You can subscribe to DataChant and download the solution files C12E01 – Solution.xlsx and C12E01 – Solution.pbix from the subscribers folder.

Exercise 12-2 Translating Multiple Messages

In this exercise, you will convert the Translate query into a function so that you can reuse it on multiple messages. Recall that while the Translator Text API allows you to send 25 messages in a single API call, this approach is not sufficient for sending massive numbers of messages. In this exercise you will learn how to translate large numbers of messages, but to keep the implementation simple, you will still send only one message per API call.

1. Open your saved workbook or Power BI report from Exercise 12-1.
2. Launch the Power Query Editor.
3. Follow these steps to create query parameters that will be used later instead of the hardcoded text and language that were used in Exercise 12-1:
   1. Handling the text parameter in the request body:
      • On the Home tab, expand the Manage Parameters drop-down and select New Parameter.
      • When the Parameters dialog box opens, in the Name box, enter InputText; and then, in the Current Value box, enter Hola Mundo. Then click OK.
   2. Handling the to parameter:
      1. On the Home tab, expand the Manage Parameters drop-down and select New Parameter.
      2. When the Parameters dialog box opens, in the Name box, enter TranslateToLanguage.
      3. In the Current Value box, enter en. Then click OK.
4. In the Queries pane, select the Translated Message query and then select Advanced Editor.

5. Next, you will modify the M formula and replace the hardcoded values with the new parameters from step 3. Here is part of the expression you will find in the Advanced Editor (Note that the expression here is indented differently, to make the formula more readable)

= Json.Document(
    Web.Contents("https://api.cognitive.microsofttranslator.com",[
        RelativePath = "translate",
        Query = [
            #"api-version" = "3.0",
            to = "en"
        ],
        Headers = [
            #"Ocp-Apim-Subscription-Key" = APIKey,
            #"Content-Type" = "application/json"
        ],
        Content=Json.FromValue({[Text = "Hola Mundo"]})
    ])
)

6. Replace the text “Hola Mundo” with InputText and the text “en” with TranslateToLanguage. Here is the modified formula:

= Json.Document(
    Web.Contents("https://api.cognitive.microsofttranslator.com",[
        RelativePath="translate",
        Query = [
            #"api-version" = "3.0",
            to = TranslateToLanguage
        ],
        Headers = [
            #"Ocp-Apim-Subscription-Key" = APIKey,
            #"Content-Type" = "application/json"
        ],
        Content = Json.FromValue({[Text = InputText]})
    ])
)

7. Select the last step in Applied Steps and ensure in the Preview pane that you still get the translated Hello World. This will help you verify that the changes you have made are correct.
8. You are now ready to convert the Translated Message query into a custom function. In the Queries pane, right-click the Translated Message query and select Create Function.
9. When the Create Function dialog box opens, in the Function Name box, enter FnTranslate and then click OK to close the dialog box.

You can now load a table with multiple messages, and translate them to English.

10. While the Power Query Editor is still open, download the workbook C12E02.xlsx from here, and save it in C:\Data\C12\.
11. On the Power Query Editor Home tab, select New Source, File, Excel.
12. When the Import Data dialog box opens, navigate to C:\Data\C12\ and select C12E02.xlsx.
13. When the Navigator dialog box opens, select the table TextForTranslation and click OK.
14. In the Power Query Editor, rename the new query Translated Messages.
15. While the Translated Messages query is selected in the Queries pane, select Invoke Custom Function on the Add Column tab.
16. When the Invoke Custom Function dialog box opens, set Function Query to FnTranslate.
17. Notice that the Text for Translation column was incorrectly populated as APIKey. Unfortunately, you cannot assign parameters as function arguments via the Invoke Custom Function dialog box. For now, keep Text for Translation as APIKey – You will fix it shortly.
18. Select Column Name in the InputText drop-down. Notice that Text for Translation is populated in the drop-down menu to the right.
19. At the bottom of the Invoke Custom Function dialog box, enter en in the text box below TranslateToLanguage. Click OK to close the dialog box.
20. Rename the FnTranslate column Translated Text.

In the formula bar, you can find the following formula:

= Table.AddColumn(#"Changed Type", "FnTranslate", each FnTranslate([Text for Translation], [Text for Translation], "en"))

21. Change it to the following formula by replacing the first instance of [Text for Translation] with the APIKey parameter, as shown here:

= Table.AddColumn(#"Changed Type", "FnTranslate", each FnTranslate(APIKey, [Text for Translation], "en"))

Depending on your privacy settings in the Query Options dialog box, you will most likely encounter the following notification warning in the Preview pane of the Power Query Editor:

Information is required about data privacy.

22. Click Continue. The Privacy Levels dialog box opens. Here you can set the privacy levels of your data sources. To ensure that you don’t upload confidential data to untrusted sources, you can set the privacy levels for the file C12E02.xlsx and Cognitive Services:
    1. Select c:\ in the first drop-down menu and set the privacy level to Organizational.
    2. Set the privacy level of https://api.cognitive.microsofttranslator.com as Organizational.
    3. Click Save.

 

Tip: If you accidentally set the privacy levels for your data sources to different levels, such as Public and Organizational, the query will not work. In such cases, whenever you wish to reset the privacy levels, you can select Data Source Settings on the Power Query Editor Home tab and reset the permissions of the two data sources in Global Permissions view and Data Sources in Current Workbook.

23. You can now see the translated text in the Preview pane of the Power Query Editor. All your messages were translated to English. Hello World! You can now close and load the query. If you use Power BI Desktop, save your report. In the next section you will learn how to share it without exposing the API key.

You can subscribe to DataChant and download the solution files C12E02 – Solution.xlsx and C12E02 – Solution.pbix from the subscribers folder.

---

---

To learn more about my book “Collect, Combine and Transform Data using Power Query in Excel and Power BI,” go here.