In this blog post, I am going to show how to use the Azure ML web service in Power BI (Power Query).

First Step:

You need to create a model in Azure ML Studio and create a web service for it.

The traditional example in Predict a passenger on Titanic ship is going to survived or not?

we have a dataset about passengers like their age, gender, and passenger class, then we are going to predict whether they are going to survive or not

Open Azure ML Studio and follow the steps to create a model for predicting this. Navigate to Azure ML Studio.

Then download the dataset for titanic from here

click on the Database in Azure ML and upload the Titanic dataset from your local machine into the Azure ML.

Then import from your local PC

Then, you able to see the imported dataset in your Azure ML dataset list

Next, we start to create an experiment in Azure ML by clicking on the Experiment in the left side of the window.

as have been explained before, the Azure ML is a drag and drop environment. in the blank experiment click on the dataset and choose the Titanic and drag and drop into the Experiment area

Now you have a dataset in the experiment and you able to create a model there. you able to explore the dataset by clicking on the output node and choose the visualization option.

Next, we are going to select some of the columns not all of them, we do not need the price, far, cabin and some descriptive data. we only need Survived, Age, Sex, and Passenger class from all 11 columns. There is a module under the transformation tab in the left side.

then click on the select Columns in Dataset and on the right side of the window, there is a property panel. Choose the four required column

Then, we need to put a model for machine learning on, in this experiment I choose a model name two-class decision forest which is an algorithm help me to predict whether will be survived or not.

Then based on the machine learning process we need to split the data into train and test dataset.

after splitting the data, then you need to train the mode using a module name Train Model. drag and drop this module into the Azure ML experiment area, then connect it as shown in the below picture. finally, click on the Train Model module and in the right side choose the Select Column to choose the Survived column.

Then, we need to to test the result using  Score Model.

Now out simple model is created we need to run it first

After running the code, we able to create a web service that we then using it in Power BI (Power Query).

Create Web Service

Now we need to create a web service for this model, by clicking on the Setup Webservice. 

Next, the web service is created and we have a node for input and other for output.

then click on the Deploy Webservice option to deploy the web service to be used for prediction. However, before doing that, we need to set up better input and output.

The input should be just

age, Sex, and Pclass 

The output should be just: Score and the probability

to do that we need to change the experiment a bit by adding the select column for both input and output node. To this aim, the final diagram for machine learning looks like below picture.

After creating the diagram, there is a need to run the model again and then push the Deploy web Service bottomn.

Next, you have a web service for the titanic problem. Now, you will navigate to a new page that shows the detail information about the web service.

Now we have the API, the next step is to get some identification from the Azure ML environment.

the first parameter is Workspace ID 

the Authentication

Then the name of the service

Power BI

now we created a model, besides we have the workspace and authentication id as well.

Now we are going to apply this model to the Data.

first, import the data into Power BI, Powe Query

Now we need to navigate to  Run R Script,

In the R editor write the below

the first line is allocating the workspace id from Azure environment to a variable name wisd

Next, it is going to store the Authentication to the auth.

finally, the web service name will store in the variable  name Service Name

Next, using some R packages will be used and the service will be called to connect to Azure ML environment

wsid = "<Workspace ID from Azure environment >" 
 
auth = "<Authentication from Azure environment>"
          
serviceName = "Titanic Video API [Predictive Exp.]"
 
library("AzureML")
 
ws <- workspace(wsid,auth)
 ds <- consume(services(ws, name = serviceName),dataset)
ds<-data.frame(ds,dataset)
#output<-data.frame(ds$Sentiment,ds$Score)

Next, hit the OK bottom. now you able to see the result

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.