Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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
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.
@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! 🙂
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
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
@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/
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |