none
Power Query to consume Azure ML Web Service

    Question

  • I've been struggling with creating a Power Query to consume an Azure ML web service and believe it's probably time for help from the community.  

    The Azure ML web service needs the following:

    • OData Endpoint Address
    • API Key
    • JSON formatted body
    • JSON formatted response

    Chris Webb's post (http://cwebbbi.wordpress.com/2014/04/19/web-services-and-post-requests-in-power-query/) gets most of the way there, but I'm missing something.

    My sample Azure ML Web Service API:  https://ussouthcentral.services.azureml.net/workspaces/ca45b0a19ee14a95aecde329dcb6c2b9/services/71d0c6db78214e63b63e335e07f1dd7d/score/help

    Any thoughts or examples that could be provided would be appreciated.


    Anthony Martin | www.pragmaticworks.com

    Friday, October 31, 2014 1:40 PM

Answers

  • The blog post below does an excellent job showing the detailed M code to call an ML web service from Power Query.  In the post global parameters aren't used, but the "placeholder" is there to add them.

    http://rquintino.wordpress.com/2014/11/26/azureml-web-service-scoring-with-excel-and-power-query/


    Anthony Martin | www.pragmaticworks.com

    Tuesday, December 02, 2014 10:03 PM

All replies

  • Update:  

    If I pass in the OData EndPoint Address into the prompt from selecting a Power Query OData source I'm prompted to supply credentials.  There are different 'types' of credentials (Anonymous, Windows, Basic, Web API, Marketplace key, and Organizational Account).  Web API seems the most relevant to me, but it the message says the credentials are invalid.

    Choosing Anonymous allows me to see that Power Query can see a function and the parameters it is expecting.  Problem is that it won't allow me to call the function without proper credentials.


    Anthony Martin | www.pragmaticworks.com

    Friday, October 31, 2014 2:07 PM
  • If I remember correctly, you need to pick "Basic" and then leave the user name blank (or put in an arbitrary value) and put the key as the password.
    Friday, October 31, 2014 2:42 PM
    Owner
  • If you need to do a POST, you won't be able to use the Power Query credential mechanism. Instead, you'd have to build the authentication header yourself. (This is for security reasons; the details are uninteresting.)

    Web.Contents(url, [Content=..., Headers=[Authorization=Binary.ToText(Text.ToBinary(":APIKEY"), BinaryEncoding.Base64)]]

    Where APIKEY should be replaced by your API key.

    Friday, October 31, 2014 2:49 PM
    Owner
  • Hi Curt, and how do I pass input parameters to a ML web service? I need to pass some parameters to an experiment I created and I´m trying to use the code below...

    How do I need to change the code below to pass along 6 input parameters? Many thanks, Daniel

    let
    Source = Json.Document(Web.Contents("https://ussouthcentral.services.azureml.net/odata/workspaces/3a1d1f26c7de4ab2984b017cfed66a07/services/28fecca0a77d484bbe76e2e266ee9100/score",
    [Headers=
    [#"Authorization"= "Bearer h8EMTuSgrg3nWj6KGLw0qFKhpy1xYPOcdpjnFnOzuVOxQZWcSMP4H1O19JhsN0UwKoT2kcL7sNZsVA70F2I68w==",
    #"Content-Type"= "application/json"]]
    ))
    in
        Source

    Tuesday, December 02, 2014 9:18 PM
  • The blog post below does an excellent job showing the detailed M code to call an ML web service from Power Query.  In the post global parameters aren't used, but the "placeholder" is there to add them.

    http://rquintino.wordpress.com/2014/11/26/azureml-web-service-scoring-with-excel-and-power-query/


    Anthony Martin | www.pragmaticworks.com

    Tuesday, December 02, 2014 10:03 PM
  • This will probably also help you:

    http://blog.gbrueckl.at/2016/06/score-powerbi-datasets-dynamically-azure-ml


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com



    Tuesday, June 21, 2016 8:10 PM