Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Azure Machine Learning - Python Query Script with Power BI dataset

Hi.

Currently I've created a machine learning experiment and deployed, and want to use dataset in Power BI as input, and consume the data from Azure Machine Learning. Currently using the new Azure Machine Learning Preview. I have created a real.time Endpoint, got the URL and keys, also Python script. I can test and get data from Azure ML.

azureml-consume.PNG

Now i want to use data in a dataset as input, and get a new column with the result from the ML.
In Power BI query, i select Pythn Script, and paste the script from Azure Machine Learning. It seems to connect, but guess i need an output section in the script. I'm not an experienced python programmer, but have programmed different solutions.

powerbi-puthonscript.PNG

 

I hope someone could give me the push to consume the Azure ML service.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

This post started with how to code Python to consume Azure ML predictive model. I ended up with scripting in "R", as i found good examples.
1: Have a Azure ML predictive model
2: Want to use Power BI to consume and get the prediction from azure ML (web service)

3: Add the prediction to the original dataset in Power BI.

 

1. Added datasource in Power BI.

2. Edit Queries

3. Transform, and choose "Run R- script"

Added this code (dataset is Power BI data, you get it directly):

 

# 'dataset' holds the input data for this script
library("RCurl")
library("rjson")
library("bitops")
# Accept SSL certificates issued by public Certificate Authorities
options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))
dataset <- dataset[complete.cases(dataset), ] ##remove NaN
h = basicTextGatherer()
hdr = basicHeaderGatherer()
req =  list(
  Inputs = list(
    input1 = setNames(lapply(split(dataset, seq(nrow(dataset))), FUN = as.list), NULL)
  ),
  GlobalParameters = setNames(fromJSON('{}'), character(0))
)
body = enc2utf8(toJSON(req))
api_key = "<replace key>" # Replace this with the API key for the web service
authz_hdr = paste('Bearer', api_key, sep=' ')
h$reset()
curlPerform(url = "<replace url>", # Replace this with the URL for the web service
            httpheader=c('Content-Type' = "application/json", 'Authorization' = authz_hdr),
            postfields=body,
            writefunction = h$update,
            headerfunction = hdr$update,
            verbose = TRUE
)
headers = hdr$value()
httpStatus = headers["status"]
if (httpStatus >= 400){
  result.error <- data.frame("Error")
}else{
  result.json = h$value()
  result.list = fromJSON(result.json)$Results$output1
  result.list.final <- lapply(result.list, FUN = function(x) lapply(x, FUN = function(y) if( is.null(y) ){y <- NA}else{y <- y}))
  result.df <- data.frame(do.call(rbind.data.frame, result.list.final))
  result.df = merge(dataset,result.df,by.x="row_id",by.y="row_id") #to combine original dataset with prediction
}

 

 
Found most of this code in this website:
 
In this way, it is possible to consume Azure ML without Premium license.

View solution in original post

10 REPLIES 10

Hi @Anonymous ,

 

maybe that's an easier solution.

https://docs.microsoft.com/en-us/power-bi/desktop-ai-insights#using-azure-ml

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi, and thanks for answering my question. Yes I have tried your solution, and it did not work. Don't know if it is a license issue or problem with my Azure ML (Tried both Azure Classic and new Azure ML Preview). It is similar to this solution: https://docs.microsoft.com/en-us/power-bi/service-machine-learning-integration and this method requires Premium license, i've got Power BI Pro license. Was able to connect Excel to the classic model, so the ML model should work. Also found this method, and have hopes that this could work with Pro license: https://powerbi.microsoft.com/de-de/blog/power-bi-azure-ml/ This is a R script, but guess it could work without writing to SQL also, just use the dataset.

Hi @Anonymous,

 

the Azure ML integration should not require premium.

Look at this video 8:30

https://www.youtube.com/watch?v=FsK8OYagidY

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi. Thanks again.

I see the comments on the video, alot of them says you need Premium. Is that not correct? 

Hi @Anonymous ,

 

if I understand Sarina Stevens (Program Manager) correctly, Power BI Premium is needed only for the Cognitive services functions (Text Analytics & Vision).

But not for your custom model from Azure ML. 

https://powerbi.microsoft.com/de-de/blog/easy-access-to-ai-in-power-bi-preview/

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Hi @Anonymous 

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi again, and thanks for helping me. 

Sorry, busy building Power BI and also having exam. I'm still not able to connect to Machine Learning from my PRO license on Power Bi desktop.

powerBiMLerror.PNG

I get this message. It would be useful if someone can confirm and have connected to Azure ML with PRO license. It might be that I've not configured Azure ML correct.

 

But a killer function if it works with PRO license.

Hi @Anonymous,

 

has your question been answered?

Did you solve your problem?


If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

This post started with how to code Python to consume Azure ML predictive model. I ended up with scripting in "R", as i found good examples.
1: Have a Azure ML predictive model
2: Want to use Power BI to consume and get the prediction from azure ML (web service)

3: Add the prediction to the original dataset in Power BI.

 

1. Added datasource in Power BI.

2. Edit Queries

3. Transform, and choose "Run R- script"

Added this code (dataset is Power BI data, you get it directly):

 

# 'dataset' holds the input data for this script
library("RCurl")
library("rjson")
library("bitops")
# Accept SSL certificates issued by public Certificate Authorities
options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))
dataset <- dataset[complete.cases(dataset), ] ##remove NaN
h = basicTextGatherer()
hdr = basicHeaderGatherer()
req =  list(
  Inputs = list(
    input1 = setNames(lapply(split(dataset, seq(nrow(dataset))), FUN = as.list), NULL)
  ),
  GlobalParameters = setNames(fromJSON('{}'), character(0))
)
body = enc2utf8(toJSON(req))
api_key = "<replace key>" # Replace this with the API key for the web service
authz_hdr = paste('Bearer', api_key, sep=' ')
h$reset()
curlPerform(url = "<replace url>", # Replace this with the URL for the web service
            httpheader=c('Content-Type' = "application/json", 'Authorization' = authz_hdr),
            postfields=body,
            writefunction = h$update,
            headerfunction = hdr$update,
            verbose = TRUE
)
headers = hdr$value()
httpStatus = headers["status"]
if (httpStatus >= 400){
  result.error <- data.frame("Error")
}else{
  result.json = h$value()
  result.list = fromJSON(result.json)$Results$output1
  result.list.final <- lapply(result.list, FUN = function(x) lapply(x, FUN = function(y) if( is.null(y) ){y <- NA}else{y <- y}))
  result.df <- data.frame(do.call(rbind.data.frame, result.list.final))
  result.df = merge(dataset,result.df,by.x="row_id",by.y="row_id") #to combine original dataset with prediction
}

 

 
Found most of this code in this website:
 
In this way, it is possible to consume Azure ML without Premium license.

Hi @Anonymous ,

 

I think I get it ...

AzureML.png

You need a "Schema discovery for Machine Learning models"

https://docs.microsoft.com/en-us/power-bi/service-machine-learning-integration#schema-discovery-for-machine-learning-models

https://docs.microsoft.com/en-us/azure/machine-learning/service/how-to-deploy-and-where#optional-automatic-schema-generation

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.