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
