Help with posting content to azure ml web services

[Anonymous]

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.

[dkay84_PowerBI]

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. 

[Anonymous]

@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! 🙂

[dkay84_PowerBI · Accepted solution]

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

[Greg_Deckler]

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/

[leokarpa]

---

@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

[Anonymous]

@Greg_Deckler  I will defo try R solution next!