Azure Machine Learning Call API from Power Query

This was my longtime aim to create this post, and finally, I will write about it here on how to call Azure ML Rest API inside Powe Query even if you don’t have Power BI Premium.
Of course, some limitations on the number of API calls need to consider while using your Rest API.
Thanks to Chris Web and Imke Blog, and Reza Rad input and working with Postman ( to test my API call)

What is Azure ML Rest API
Azure ML workspace you able to create a web service able to be called in other applications. After creating your model, you can create an endpoint ( Url and APIKey), so your model is consumed in other applications.

Before this, for Power BI premium capacity, you can create a web service ( endpoint) and then use the Azure Machine Learning in Powe Query to call it back. This is good if you have a Power BI premium account aligned with Azure Active Directory.

What if we want to create an endpoint and directly call it from Power Query without a premium account? Also, this can be a practice for other ML/ AI API calls.


Call API inside Powe Query
for any JSON call, I need a Url, API key, and Header; I got help from the Postman application to create this connection.
My API is a Post Type; it requires the Body to pass the input data and has Headers for Content_type and Authorization.

All of the information about API calls ( Body header, content_type) can be obtained from Costume Page from Consumption types (R, C#, and Python)

I create a blank Query first, then in the source section, I use the Web. Contents function to call the API, according to Chris Web blog, for headers, I define two variables #”Authorization” and #”Content-Type.” for Authorization beside API key I need to add “Bearer” (according to the C# page), and content_type is “application/JSON)

the next step is Content

for the content part; I need to convert the JSON request as a binary ( again, I use the input request in the c# page)

so the final web. Content call would be as below

Source = Web.Contents(uri,[
Headers=[#"Authorization" = "Bearer API Key", #"Content-Type"="application/json"],Content=Text.ToBinary("{""Inputs"":{""data"":[{""Pclass"":0,""Sex"":""example_value"",""Age"":0}]},""GlobalParameters"":{""method"":""predict""}}")
])

As you can see, you need to pass URI as a variable; in the headers part, we need to define two variables Authorization and Content_type. Then for the web part, we have to send the body that holds the input data

let
uri=”API Url”,
Source = Web.Contents(uri,[
Headers=[#”Authorization” = “Bearer APIKey”, #”Content-Type”=”application/json”],Content=Text.ToBinary(“{“”Inputs””:{“”data””:[{“”Pclass””:0,””Sex””:””example_value””,””Age””:0}]},””GlobalParameters””:{“”method””:””predict””}}”)
])
in
Source

This just confirmed it working,

still, I got the below message


In the code, I need to convert it to JSON format so I can process it inside Power bI as JSON file,

result=Json.Document(Source),

so the enhanced code would be as below, by converting it to a table and expanding the value

let
 
uri="API Url",
Source = Web.Contents(uri,[
Headers=[#"Authorization" = "Bearer API", #"Content-Type"="application/json"],Content=Text.ToBinary("{""Inputs"":{""data"":[{""Pclass"":0,""Sex"":""example_value"",""Age"":0}]},""GlobalParameters"":{""method"":""predict""}}")
]),
result=Json.Document(Source),
#"Converted to Table" = Record.ToTable(result),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value")
in
#"Expanded Value"

Lets make it functional so we able to call it for different API call, URl
Create a Function call and Parameters

you can define Parameters or variables to make it functions

I need to create variables/ Parameters for data input, API, and URL

so I define five here as I have three inputs and 2 for API and Url

So replaced all hard code with these variables as below.

let
       
    Source = Web.Contents(Url,[
     Headers=[#"Authorization" = APiKey, #"Content-Type"="application/json"],
     Content=Text.ToBinary("{""Inputs"":{""data"":[{""Pclass"":"& PassengerClass&",""Sex"":"""& Gender&""",""Age"":"& Age &"}]},""GlobalParameters"":{""method"":""predict""}}")
  ]),
  result=Json.Document(Source),
    #"Converted to Table" = Record.ToTable(result),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value")
in
    #"Expanded Value"

Now I will create a function from all steps I did to call a custom call out of it.

Now the easy part,
I import the dataset and add columns and add a custom function.

and after the call
I can see

Pros and cons: the speed can be slow as the number of pages increases; maybe if you do not have data each time, disable the refresh while loading the data, so the function doesn’t call all time. And also, the limitation on API calls needs to be checked beforehand.
See the video on this

Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

Leave a Reply