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
