Getting API results from Azure Machine Learning into Power Query

image

If you are into cool new technologies then this blog post is for you.

A few weeks ago, Microsoft released a new service called Azure Machine Learning or simply #AML. It’s a cloud service that let’s you create experiments for data prediction and machine learning in general. (What Data Scientists do)

You can read more about this service here (you can try it for FREE!!). What really has me hooked to this is the fact that it you can integrate r, so you can create your R code and even packages and use them on the cloud without doing anything special – an enfortless system bound to make things happen (or predict what would happen).

This type of service is the one that saves hundreds of hours of development or tedious coding so I recommend you give it a try Sonrisa

This blog post is based on this sample experiment

We have the experiment ready – let’s use the API and grab some results with Power Query!

Step 1: Check your Web Service

Before checking the web service, let’s see what the output of that Web Service should be:

image

I’ve chosen the model evaluation to be the output of my web service. You can pick any of the events to be your web service output just by right clicking on the output of those events and setting them as the output. Now we know what we should be able to see from the API.

By now, you should know that Power Query is an Excel add-in and part of the Power BI family. It’s the tool that lets you connect to virtually any type of source (almost!). Also, you should’ve published your  Web service for the experiment in question.

This time we are going to use Power Query to grab the results from the AML API, but first let’s make sure that the web service is running:

image

(web services list from the AML {studio})

Now let’s find out more information about the web service. Let’s click on it:

image

when clicking the API Help page for the REQUEST/RESPONSE, you get a webpage similar to this:

image

at the bottom of the page you get a sample code for:

  • C#
  • Python
  • R

now, I have all of them installed (R studio, Visual Studio and Python), but I’m an Excel kind of person so I want to get this into Excel somehow….and Power Query is the answer Sonrisa

Step 2: Write the Query in Power Query

Before we proceed, we need to make sure that we have the following information at hand:

  • ODATA Endpoint Address
  • API Key

once you have it then go ahead and use the ODATA Endpoint address url as the web url source:

image

and the first time that you do it you’ll get an error like this:

image

which just means that something iss wrong (doesn’t really tell you what and we are not here to assume a thing).
If you tried to input the API Key through the credentials window you’ll also get an error saying that the API Key is invalid so, instead, we’ll be going to the VIEW tab and choose the advanced editor and write our own M code:

image

(Launching the Advanced Editor)

and replace that code with the following one:

let
Source = Json.Document(Web.Contents(“[your End Point address url goes here]/score”,
[Headers=
[#”Authorization”=
“Bearer [your API key goes here]”,
#”Content-Type”=
“application/json”]]
))

in
Source

Note: don’t forget to add the /score right at the end of your End Point address url!

you can also try this one:

let
Source = Web.Contents(“[your End Point address url goes here]/score
,[Headers=
[#”Authorization”=
“Bearer [your API key goes here]”,
#”Content-Type”=
“application/json”]]
),

#”Imported JSON” = Json.Document(Source)

in
#”Imported JSON”

and this should be the result:

image

After this, you can just dump the results into an Excel worksheet and start analyzing or use it as you wish Sonrisa

The possibilities!

Now we know that we can connect to the API service. The experiment that we used didn’t need any parameters/inputs from us, but what if the experiment needed some input from us? Perhaps we could parse the needed parameter input into Power Query and get the results that we need based on an specific M code ! Sonrisa

What about combining the results of MULTIPLE experiments into one big report in Excel?

There are A LOT of opportunities here so I’ll wrap up this blog post and start looking at them. Expect some more posts about this soon.

Also, did I mention that you can use Odata feeds created from Power Query as a data source for experiments in AML? image

Posted in: