cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DomPower Frequent Visitor
Frequent Visitor

Microsoft Translator Support?

Hi,

 

I was planing to use the Microsoft Translator support for translating text in Power BI. Similar to the following example: https://www.youtube.com/watch?v=jZQ4BZmtseo . Base on the post https://community.powerbi.com/t5/Desktop/Sample-Power-BI-multilingual-support-report/td-p/57354 it seems to be that Microsoft Translator is not supported anymore. is that correct? Is there another way to use the API of Microsoft Translator?

 

Thanks a lot.

 

Cheers,

Dom

1 ACCEPTED SOLUTION

Accepted Solutions
DomPower Frequent Visitor
Frequent Visitor

Re: Microsoft Translator Support?

Hi @v-huizhn-msft,

 

I was able to fix that issue.

 

First, to avoid the issue  "A web API key can only be specified when a web API key name is provided" I had to set the "Data source settings" to "Anonymous". Because the M code is providing the API key logic there is no need to specify the key in the GUI of Power BI.

 

Second, in Power BI are two kinds of Data source settings: "Data source in current file" and "Global permissions". As far as I experienced it, the permission settings for the URL I'm using should be the same in either Global or Current File setting. Not sure if this is 100% correct but it worked for my case. Therefore, I set it credential type to "Anonymous" and privacy level to "None".

 

02-11-2017 13-45-32.jpg

 

Afterwards I received the error message "Formula.Firewall: Query references other queries or steps, so it may not directly access data source. Please rebuild this data combination". To get rid of this error I set Power BI to ingore the Privacy in either Global or Current File setting under Option as mentioned in http://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-or-steps/td-....

 

large.png

 

Now, the translation feature is working without any errors.

 

Thanks for your help.

 

Cheers,

Dom

View solution in original post

7 REPLIES 7
v-huizhn-msft Super Contributor
Super Contributor

Re: Microsoft Translator Support?

Hi @DomPower,

You can use Translator Text API in Power BI, more details, please follow the guide in the blog: Translate Text In Power BI Or Excel With Microsoft Translator Text API.

Best Regards,
Angelia

Highlighted
DomPower Frequent Visitor
Frequent Visitor

Re: Microsoft Translator Support?

Hi @v-huizhn-msft,

 

Thanks for your support.

I read the blog post and tried to establish a connection by using the template DATACHANT is providing.

Unfortunately, I get the following error message when I fill in my Ocp-Apim-Subscription-Key: "A web API key can only be specified when a web API key name is provided". I am aware of serveral post discussing that issue but I still not sure what to do about it.

The function I am using to get the translater access looks like this:

 

let
    EmptyContent = Text.ToBinary("", TextEncoding.Ascii),
    
    Response =
        Web.Contents("https://api.cognitive.microsoft.com/sts/v1.0/issueToken",
            [
                Headers =
                    [
                        #"Content-Type"="application/json",
                        Accept="application/jwt",
                        #"Ocp-Apim-Subscription-Key"= TranslatorKey
                    ],
                Content=EmptyContent
            ]),
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(Response,null,null,20127)}),
    Res = #"Imported Text"{0}[Column1]
in
    Res

 

 

The function to translate the text looks like this:

 

(textToTranslate, TranslateTolanguage) =>    
let
    URL = "https://api.microsofttranslator.com/v2/http.svc/Translate?appid=Bearer " & GetTranslatorAccessToken & "&text=" & textToTranslate & "&to=" & TranslateTolanguage,
    Response = Web.Contents(
        URL,
        [
            Headers = [Accept="application/xml"]
        ]
    ),
    XML = Xml.Tables(Response, null, 65001),
    TranslatedText = XML{0}[#"Element:Text"]
in
    TranslatedText

 

Apart from that, I already checked if my Ocp-Apim-Subscription-Key is valid by using http://docs.microsofttranslator.com/oauth-token.html#/ . So far the key is valid since I received the response code 200.

BTW the variable TranslatorKey is stored as a parameter in Power BI.

 

Do you have any suggestions?

 

Cheers,

Dom

v-huizhn-msft Super Contributor
Super Contributor

Re: Microsoft Translator Support?

Hi @DomPower,

The error means you need to specify an API key name in the headers area of Web.Contents using the advanced editor. I am not specific with API which is a part of developer,  please see Chris Webb's post on this for a good example: https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/.  Or please create a new thread in Power BI developer community, where you can get professional support.  And welcome to share your workaround if you get answer.

Best Regards,
Angelia

DomPower Frequent Visitor
Frequent Visitor

Re: Microsoft Translator Support?

Hi @v-huizhn-msft,

 

I was able to fix that issue.

 

First, to avoid the issue  "A web API key can only be specified when a web API key name is provided" I had to set the "Data source settings" to "Anonymous". Because the M code is providing the API key logic there is no need to specify the key in the GUI of Power BI.

 

Second, in Power BI are two kinds of Data source settings: "Data source in current file" and "Global permissions". As far as I experienced it, the permission settings for the URL I'm using should be the same in either Global or Current File setting. Not sure if this is 100% correct but it worked for my case. Therefore, I set it credential type to "Anonymous" and privacy level to "None".

 

02-11-2017 13-45-32.jpg

 

Afterwards I received the error message "Formula.Firewall: Query references other queries or steps, so it may not directly access data source. Please rebuild this data combination". To get rid of this error I set Power BI to ingore the Privacy in either Global or Current File setting under Option as mentioned in http://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-or-steps/td-....

 

large.png

 

Now, the translation feature is working without any errors.

 

Thanks for your help.

 

Cheers,

Dom

View solution in original post

JulianPayne_i4 Frequent Visitor
Frequent Visitor

Re: Microsoft Translator Support?

This did not solve my issue...

 

coding in M Query language...

 

My code to create a custom translation function:

 

let
Source = Web.Contents("https://api.cognitive.microsoft.com/sts/v1.0", [Headers=[#"Ocp-Apim-Subscription-Key"="xxxxxxx"]])
in
Source

 

My result:


404 Error Resource Not found.....

DataSource.Error: Web.Contents failed to get contents from 'https://api.cognitive.microsoft.com/sts/v1.0' (404): Resource Not Found
Details:
DataSourceKind=Web
DataSourcePath=https://api.cognitive.microsoft.com/sts/v1.0
Url=https://api.cognitive.microsoft.com/sts/v1.0

 

 

I have tried many other names for the api key [Header]:
apikey
apitoken
Authorization

 

 

Why is the resource not found?

 

In Azure, the subscription gave me the url which I copy and pasted. I see other users using this URL - they have also had problems connecting.

 

I chose "Anonymous" authentication, as has been indicated.

JulianPayne_i4 Frequent Visitor
Frequent Visitor

Re: Microsoft Translator Support?

I still have the same issue...

 

coding in M Query language...

 

My code to create a custom translation function:

let
Source = Web.Contents("https://api.cognitive.microsoft.com/sts/v1.0", [Headers=[#"Ocp-Apim-Subscription-Key"="xxxxxxx"]])
in
Source

 

My result:
404 Error Resource Not found.....

 

DataSource.Error: Web.Contents failed to get contents from 'https://api.cognitive.microsoft.com/sts/v1.0' (404): Resource Not Found

Details:
DataSourceKind=Web
DataSourcePath=https://api.cognitive.microsoft.com/sts/v1.0
Url=https://api.cognitive.microsoft.com/sts/v1.0

 

 

I have tried many other names for the api key [Header]:
apikey
apitoken
Authorization

 

 

 

Why is the resource not found?

 

In Azure, the subscription gave me the url which I copy and pasted. I see other users using this URL - they have also had problems connecting.

 

I chose "Anonymous" authentication, as has been indicated.

JulianPayne_i4 Frequent Visitor
Frequent Visitor

Re: Microsoft Translator Support?

this is a basic query that works - doesn't do exactly what i need to do, but at least i can all the api...

 

let

 

body =  "[{""Text"":""Have a good day""}]",

 

jsonContent = Text.ToBinary(body, TextEncoding.Ascii),

 

source= Web.Contents(

 

"https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=en&to=fr-FR",

 

[

 

Headers=

 

[#"Ocp-Apim-Subscription-Key"="xxxxxxxxxxxxx",

 #"Content-Type"="application/json", 

 #"Accept"="application/json"],

 

Content=jsonContent

 

]

 

),

 

json = Json.Document(source),

 

    json1 = json{0},

 

    translations = json1[translations],

 

    translations1 = translations{0}

 

 

in

    translations1

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 221 members 2,410 guests
Please welcome our newest community members: