Using Cognitive service for Translation in Power BI

There is a possibility to call cognitive service for translation inside Power Query.

I will use this to translate 3000 rows of data about people arrested in Iran for protesting; This information contains city Name, Fullname and Other statements.

In this article, I will show how to call cognitive services for translation, create a proper JSON call and finally, use it inside Power Query.

Create JSON call and Link

I am using really good tools to create a proper link for the JSON call name Postman

there is an application that you can download.

In the application, I select the call method to be POST

; then I paste the original URL as

https://api.cognitive.microsofttranslator.com/translate

The URL need some Parts, such as the version of the API and the origin and destination language for translation.

In this example, the api-version is three from fa, that is, Farsi to English (en)

Now I need to add Headers and Body

headers include the API key and content type, plus the cognitive service region

if you want to find them in your Azure Account, please check the below picture

In the next step, I add all three headers to the Headers section below

The last step is to create a body example in the same section

[{
‘text’: “علی”
}]

Now test the API by clicking on send to see the result below.

Power Query

Open Power bI desktop; on the transformation Data ( Power Query), create a blank query as below

first, in the let section, put a URL in a variable as below

Next, call a function name ” Web. content” as a result should be a JSON file, so put the result of web content in a function name “Json.Document

Next, create a header list for all Headers, such as content type, key and region

let
    url = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=fa&to=en",
    Source=Json.Document(Web.Contents(url,
     [Headers=[#"Ocp-Apim-Subscription-Key"="API key",
      #"Ocp-Apim-Subscription-Region"="region", 
      #"Content-type"="application/json"], 
      Content=Text.ToBinary("[{   ""text"": """&TexttoTranslate&"""   }]")]))
in
    Source
    

Afte this you need to create a function of that and a parameter to be able to call the function as invoke function

Invoke column function

After creating the function, you able to invoke it inside a power bi file as below

I applied the function on the name of ppl ( sometime doesn’t work as it translate the name, but 80% works, I applied on the sentence people get, their role and status, and city the come, the data was about 3000 rows of data and the APi works fine

Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

Leave a Reply