topic Re: Calling Azure ML Web Service from Power BI in Developer

Calling Azure ML Web Service from Power BI

dkay84_PowerBI — Sun, 09 Oct 2016 15:47:24 GMT

I have looked at other posts covering this topic, but none seem to explain, in a way I can comprehend, how to do what I want to acheive. I am pulling in Twitter data via API directly into Power BI. I have been using a custom function to call the Microsoft Cognitive Services Sentiment Analysis API to score the Tweets. However, I have an Azure ML Web Service for text analytics that I want to use instead of Microsoft's Cognitive Services.

I know how to see the request URL and API key for a published web service in Azure ML. I also know where to find the R code example. I am not sure how to implement a call to this API directly in Power BI (either via API or R), but also, could it be possible to do the scoring with R directly in an R script in the query editor?

Re: Calling Azure ML Web Service from Power BI

Eric_Zhang — Tue, 11 Oct 2016 08:27:57 GMT

@dkay84_PowerBI

What is your web service like? Currently Power BI doesn't support fetching data from Azure ML, you might choose "get data->web" or call an api from Power Query.

Re: Calling Azure ML Web Service from Power BI

dkay84_PowerBI — Tue, 11 Oct 2016 15:26:39 GMT

My code to call the Cognitive Services API looks as follows:

(Source as table) as any =>
let
    JsonRecords = Text.FromBinary(Json.FromValue(Source)),
    JsonRequest = "{""documents"": " & JsonRecords & "}",

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"= "MY KEY",
                           #"Content-Type"="application/json", Accept="application/json"],
                Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse

Since the Azure ML web service gives a url and key, I figured it would be possible to call it similar to the call above. However, the structure of the call and the required headers is a bit different, and trying to figure out the required pieces from the Azure ML web service API page is difficult.

Any thoughts?

Re: Calling Azure ML Web Service from Power BI

Eric_Zhang — Wed, 12 Oct 2016 07:18:06 GMT

@dkay84_PowerBI wrote:

My code to call the Cognitive Services API looks as follows:
(Source as table) as any =>
let
    JsonRecords = Text.FromBinary(Json.FromValue(Source)),
    JsonRequest = "{""documents"": " & JsonRecords & "}",

    JsonContent = Text.ToBinary(JsonRequest, TextEncoding.Ascii),
    Response =
        Web.Contents("https://westus.api.cognitive.microsoft.com/text/analytics/v2.0/sentiment?",
            [
                Headers = [#"Ocp-Apim-Subscription-Key"= "MY KEY",
                           #"Content-Type"="application/json", Accept="application/json"],
                Content=JsonContent 
            ]),
    JsonResponse = Json.Document(Response,1252)
in
    JsonResponse
Since the Azure ML web service gives a url and key, I figured it would be possible to call it similar to the call above. However, the structure of the call and the required headers is a bit different, and trying to figure out the required pieces from the Azure ML web service API page is difficult.

Any thoughts?

As far as I know, http request is not language-specific, so either Power Query or any other language can send a request with proper request header. In your case, as you don't know how to apply the url and key, which is actually Azure ML specific, then I suggest you firstly ask a question like "how to call the Azure ML web service API" in the dedicated Azure ML forum, then transplant the answer to Power Query.

Re: Calling Azure ML Web Service from Power BI

ASHOKKPIN — Thu, 24 Aug 2017 20:28:09 GMT

@dkay84_PowerBI - this code works only in desktop right? not in powerbi.com