cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Help with posting content to azure ml web services

Hello there

So I created a web service experiment that I successfully tested it through calling it via Excel.

Now I would like to call it via Power bI but I dont know how to compose my content.

 

 

let
     Score= (sp1 as any, sp2 as any, Religion as any, Marital as any, Gender as any, LiveAlone as any, Age as any, HasNeeds as any) =>

let

//note: these should be private, these serviceuri and servicekey will not be guaranteed to work, use your own and be careful when sharing! 🙂

serviceUri="azuremlserviceuri",
serviceKey="mykey"

PostContents= "

{
  ""Results"": {
    ""output1"": {
 //   ""type"": ""DataTable"",
    ""value"": {
    ""ColumnNames"": [
      ""sp1"": """&sp1&""",
      ""sp2"": """&sp2&""",
      ""Religion"": """&Religion&""",
      ""Marital"": """&Marital&""",
      ""Gender"": """&Gender&""",
      ""LiveAlone"": """&LiveAlone&""",
      ""Age"": """&Age&""",
      ""HasNeeds"": """&HasNeeds&"""
    },
    ""GlobalParameters"": {}
  }
}
        
                   
",
      //Define Wait function
    Wait = (seconds as number, action as function) =>
            if (List.Count(
             List.Generate(
              () => DateTimeZone.LocalNow() + #duration(0,0,0,seconds),
              (x) => DateTimeZone.LocalNow() < x,
              (x) => x)
              ) = 0)
               then null else action()
    //Call Wait function to wait 5 seconds
    //then return the current time again
   
,
    Source=Wait ( 0.05, () => Web.Contents(serviceUri,
 [Content=Text.ToBinary(PostContents),
 Headers=[Authorization="Bearer "&serviceKey,#"Content-Type"="application/json; charset=utf-8"]])),
    #"Imported JSON" = Json.Document(Source),
    #"Table from List" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Transposed Table" = Table.Transpose(#"Table from List")
   in
     #"Transposed Table"
    in Score

 

Then I got an error message "We cannot apply operator & to types Text and Number."

 

Where did i go wrong? Please could you assist?

Thank you very much in advance.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Help with posting content to azure ml web services

Think I figured this out.

 

1.  Create a new query from blank query

2.  Immediately change the name of the query to the respecitive name for the function code (i.e. ToAzureMLJson)

     -  This name is the very last line in the code (after "in")

3. Paste the code in and it should work

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Help with posting content to azure ml web services

I use a R Script step for this, below is the code (default R code from Azure ML with modifications. Green is add, Red is delete, Orange is modify:

 

library("RCurl")

library("rjson")

 

createList <- function(dataset)

{

  temp <- apply(dataset, 1, function(x) as.vector(paste(x, sep = "")))

  colnames(temp) <- NULL

  temp <- apply(temp, 2, function(x) as.list(x))

  return(temp)

 

}

 

# Accept SSL certificates issued by public Certificate Authorities

options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))

 

h = basicTextGatherer()

hdr = basicHeaderGatherer()

 

req =  list(

    Inputs = list(

            "input1"= list(

                list(

                        'Customer' = "",

                        'Contact' = "",

                        'Owner' = "",

                        'EstValue' = "1",

                        'EstCloseYear' = "1",

                        'EstCloseMonth' = "1",

                        'EstCloseQuarter' = "",

                        'ServiceOfferingCode' = "1",

                        'StateCode' = "1"

                    )

            )

        ),

        GlobalParameters = setNames(fromJSON('{}'), character(0))

)

 

 

req = list(

 

  Inputs = list(

   

    "input1" = list(

      "ColumnNames" = list("Customer", "Contact", "Owner", "EstValue", "EstCloseYear", "EstCloseMonth", "EstCloseQuarter", "ServiceOfferingCode", "StateCode"),

      "Values" = createList(dataset)

    )                ),

  GlobalParameters = setNames(fromJSON('{}'), character(0))

)

 

body = enc2utf8(toJSON(req))

api_key = "abc123" # Replace this with the API key for the web service

authz_hdr = paste('Bearer', api_key, sep=' ')

 

h$reset()

curlPerform(url = "https://ussouthcentral.services.azureml.net/subscriptions/[guid]/services/[guid]/execute?api-version=2.0&details=true",

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)

{

print(paste("The request failed with status code:", httpStatus, sep=" "))

 

# Print the headers - they include the request ID and the timestamp, which are useful for debugging the failure

print(headers)

}

 

print("Result:")

result = h$value()

print(fromJSON(result))

 

##Return results back

inter <- do.call("rbind", finalResult$Results$output1$value$Values)

MyFinalResults <- data.frame(inter)

names(MyFinalResults) <- finalResult$Results$output1$value$ColumnNames

 

rm(list=setdiff(ls(), "MyFinalResults "))

 

The original code for this comes from Justyna Lucznik's blog article here:

https://powerbi.microsoft.com/en-us/blog/power-bi-azure-ml/

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
Microsoft

Re: Help with posting content to azure ml web services

I use a different method that requires only M.  I can't take credit for this approach but unfortunately it's been a while since I first used this approach and so I can't remember where I found it.

 

You need to create 3 separate functions:

 

Function 1 - Sends table to Azure ML as JSON

 

let
    ToAzureMLJson= (input as any) as text => 
let

    transformationList = {
        [Type = type time, Transformation = (value_in as time) as text => """" & Time.ToText(value_in, "hh:mm:ss.sss") & """"], 
        [Type = type date, Transformation = (value_in as date) as text => """" & Date.ToText(value_in, "yyyy-MM-dd") & """"], 
        [Type = type datetime, Transformation = (value_in as datetime) as text => """" & DateTime.ToText(value_in, "yyyy-MM-ddThh:mm:ss.sss" & """")], 
        [Type = type datetimezone, Transformation = (value_in as datetimezone) as text => """" & DateTimeZone.ToText(value_in, "yyyy-MM-ddThh:mm:ss.sss") & """"], 
        [Type = type duration, Transformation = (value_in as duration) as text => ToAzureMLJson(Duration.TotalSeconds(value_in))], 

        [Type = type number, Transformation = (value_in as number) as text => Number.ToText(value_in, "G", "en-US")],
        [Type = type logical, Transformation = (value_in as logical) as text => Logical.ToText(value_in)],

        [Type = type text, Transformation = (value_in as text) as text => """" & value_in & """"],

        [Type = type record, Transformation = (value_in as record) as text => 
                            let
                                GetFields = Record.FieldNames(value_in),
                                FieldsAsTable = Table.FromList(GetFields, Splitter.SplitByNothing(), {"FieldName"}, null, ExtraValues.Error),
                                AddFieldValue = Table.AddColumn(FieldsAsTable, "FieldValue", each Record.Field(value_in, [FieldName])),
                                AddJson = Table.AddColumn(AddFieldValue, "__JSON", each ToAzureMLJson([FieldValue])),

                                jsonOutput = "[" & Text.Combine(AddJson[__JSON], ",") & "]"
                            in
                                jsonOutput
                            ],
        [Type = type table, Transformation = (value_in as table) as text => 
                            let
                                BufferedInput = Table.Buffer(value_in),
                                GetColumnNames = Table.ColumnNames(BufferedInput),
                                ColumnNamesAsTable = Table.FromList(GetColumnNames , Splitter.SplitByNothing(), {"FieldName"}, null, ExtraValues.Error),
                                ColumnNamesJson = """ColumnNames"": [""" & Text.Combine(ColumnNamesAsTable[FieldName], """, """) & """]",

                                AddJson = Table.AddColumn(value_in, "__JSON", each ToAzureMLJson(_)),
                                ValuesJson = """Values"": [" & Text.Combine(AddJson[__JSON], ",#(lf)") & "]",

                                jsonOutput = "{""Inputs"": { ""input1"": {" & ColumnNamesJson & "," & ValuesJson & "} }, ""GlobalParameters"": {} }"
                            in
                                jsonOutput
                            ],
        [Type = type list, Transformation = (value_in as list) as text => ToAzureMLJson(Table.FromList(value_in, Splitter.SplitByNothing(), {"ListValue"}, null, ExtraValues.Error))],

        [Type = type binary, Transformation = (value_in as binary) as text => """0x" & Binary.ToText(value_in, 1) & """"],


        [Type = type any, Transformation = (value_in as any) as text => if value_in = null then "null" else """" & value_in & """"]

    },


    transformation = List.First(List.Select(transformationList , each Value.Is(input, _[Type]) or _[Type] = type any))[Transformation],

    result = transformation(input)  

in
    result 

in
    ToAzureMLJson

 

 

Function 2 - Converts Azure ML JSON response to table

 

let
    AzureMLJsonToTable = (azureMLResponse as binary) as any => 
let

    WebResponseJson = Json.Document(azureMLResponse ,1252),
    Results = WebResponseJson[Results],
    output1 = Results[output1],
    value = output1[value],
    BufferedValues = Table.Buffer(Table.FromRows(value[Values])),
    ColumnNameTable = Table.AddIndexColumn(Table.FromList(value[ColumnNames], Splitter.SplitByNothing(), {"NewColumnName"}, null, ExtraValues.Error), "Index", 0, 1),
    ColumnNameTable_Values = Table.AddIndexColumn(Table.FromList(Table.ColumnNames(BufferedValues), null, {"ColumnName"}), "Index", 0, 1),

    RenameList = Table.ToRows(Table.RemoveColumns(Table.Join(ColumnNameTable_Values, "Index", ColumnNameTable, "Index"),{"Index"})),
    RenamedValues = Table.RenameColumns(BufferedValues, RenameList),

    ColumnTypeTextTable = Table.AddIndexColumn(Table.FromList(value[ColumnTypes], Splitter.SplitByNothing(), {"NewColumnType_Text"}, null, ExtraValues.Error), "Index", 0, 1),
    ColumnTypeText2Table = Table.AddColumn(ColumnTypeTextTable, "NewColumnType", each 
     if Text.Contains([NewColumnType_Text], "Int") then type number 
else if Text.Contains([NewColumnType_Text], "DateTime") then type datetime 
else if [NewColumnType_Text] = "String" then type text 
else if [NewColumnType_Text] = "Boolean" then type logical
else if [NewColumnType_Text] = "Double" or [NewColumnType_Text] = "Single" then type number
else if [NewColumnType_Text] = "datetime" then type datetime
else if [NewColumnType_Text] = "DateTimeOffset" then type datetimezone
else type any),
    ColumnTypeTable  = Table.RemoveColumns(ColumnTypeText2Table ,{"NewColumnType_Text"}),  
    
    DatatypeList = Table.ToRows(Table.RemoveColumns(Table.Join(ColumnNameTable, "Index", ColumnTypeTable, "Index"),{"Index"})),
    RetypedValues = Table.TransformColumnTypes(RenamedValues, DatatypeList, "en-US"),
    
    output = RetypedValues
in
    output 

in
    AzureMLJsonToTable

 

 

Function 3 - Calls Azure ML Web Service (utilizes 1st function to package the data as JSON)

 

let
    AzureMLJsonToTable = (optional Timeout as number) as any => 
let
    WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) ,  
    WebServiceURI = "Request POST URI",
    WebServiceKey = "API Key",
    TableToScore = TableName,
    WebServiceContent = ToAzureMLJson(TableToScore),

    WebResponse = Web.Contents(WebServiceURI,
        [Content = Text.ToBinary(WebServiceContent),
         Headers = [Authorization="Bearer " & WebServiceKey,
                    #"Content-Type"="application/json",
                    Accept="application/json"],
         Timeout = WebTimeout]),
    
    output = AzureMLJsonToTable(WebResponse)
in
    output 

in
    AzureMLJsonToTable

 

 

You need to make sure that your table has the correct data types and column names that your Azure ML web service is looking for. 

Highlighted
Post Patron
Post Patron

Re: Help with posting content to azure ml web services

@dkay84_PowerBI the first function returns an error Expression.Error: The name 'ToAzureMLJson' wasn't recognized. Make sure it's spelled correctly. Any idea?

Thanks very much! 🙂

Highlighted
Microsoft
Microsoft

Re: Help with posting content to azure ml web services

Think I figured this out.

 

1.  Create a new query from blank query

2.  Immediately change the name of the query to the respecitive name for the function code (i.e. ToAzureMLJson)

     -  This name is the very last line in the code (after "in")

3. Paste the code in and it should work

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Help with posting content to azure ml web services

@Greg_Deckler  I will defo try R solution next!

Highlighted
Frequent Visitor

Re: Help with posting content to azure ml web services


@smoupre wrote:

I use a R Script step for this, below is the code (default R code from Azure ML with modifications. Green is add, Red is delete, Orange is modify:

 

library("RCurl")

library("rjson")

 

createList <- function(dataset)

{

  temp <- apply(dataset, 1, function(x) as.vector(paste(x, sep = "")))

  colnames(temp) <- NULL

  temp <- apply(temp, 2, function(x) as.list(x))

  return(temp)

 

}

 

# Accept SSL certificates issued by public Certificate Authorities

options(RCurlOptions = list(cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl")))

 

h = basicTextGatherer()

hdr = basicHeaderGatherer()

 

req =  list(

    Inputs = list(

            "input1"= list(

                list(

                        'Customer' = "",

                        'Contact' = "",

                        'Owner' = "",

                        'EstValue' = "1",

                        'EstCloseYear' = "1",

                        'EstCloseMonth' = "1",

                        'EstCloseQuarter' = "",

                        'ServiceOfferingCode' = "1",

                        'StateCode' = "1"

                    )

            )

        ),

        GlobalParameters = setNames(fromJSON('{}'), character(0))

)

 

 

req = list(

 

  Inputs = list(

   

    "input1" = list(

      "ColumnNames" = list("Customer", "Contact", "Owner", "EstValue", "EstCloseYear", "EstCloseMonth", "EstCloseQuarter", "ServiceOfferingCode", "StateCode"),

      "Values" = createList(dataset)

    )                ),

  GlobalParameters = setNames(fromJSON('{}'), character(0))

)

 

body = enc2utf8(toJSON(req))

api_key = "abc123" # Replace this with the API key for the web service

authz_hdr = paste('Bearer', api_key, sep=' ')

 

h$reset()

curlPerform(url = "https://ussouthcentral.services.azureml.net/subscriptions/[guid]/services/[guid]/execute?api-version=2.0&details=true",

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)

{

print(paste("The request failed with status code:", httpStatus, sep=" "))

 

# Print the headers - they include the request ID and the timestamp, which are useful for debugging the failure

print(headers)

}

 

print("Result:")

result = h$value()

print(fromJSON(result))

 

##Return results back

inter <- do.call("rbind", finalResult$Results$output1$value$Values)

MyFinalResults <- data.frame(inter)

names(MyFinalResults) <- finalResult$Results$output1$value$ColumnNames

 

rm(list=setdiff(ls(), "MyFinalResults "))

 

The original code for this comes from Justyna Lucznik's blog article here:

https://powerbi.microsoft.com/en-us/blog/power-bi-azure-ml/

 


Have you tried this code? It does not work. Look at the end, you did not define the variable finalResult. It gives an error on this. I tried by several ways this code, trying to fix these problems and nothing.. it did not work. It returns a blank table. I am sure that post by Justyna and the webinar by David Brown are missing some points. Would be glad if someone came with the correct code

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors