Forum Discussion

bottos's avatar
bottos
Frequent Visitor
2 years ago
Solved

Trouble converting list into text (JSON and Azure Cognitive Services)

Hi. I am not sure what I am doing wrong, so any help will be greatly appreciated. I am using the Azure Cognitive Services to translate a column with text. To do that, I have created a function as following:
 
(input as text) =>
 
let
apikey = apiKey,
headers = [#"Ocp-Apim-Subscription-Key" = apiKey, #"Content-type" = "application/json"],
endpoint = "https://api.cognitive.microsofttranslator.com/translate?api-version=3.0&from=en&to=pt",
 
// JSON body
jsonbody = "[{ ""text"": """ & input & """ }]",
bytesbody = Text.ToBinary(jsonbody),
 
// Call the API
bytesresponse = Web.Contents(endpoint, [Headers = headers, Content = bytesbody]),
 
// Parse the JSON data
jsonresponse = Json.Document(bytesresponse),
//translation = jsonresponse,
#"Expanded Translations" = Table.ExpandListColumn(jsonresponse, "Translations"),
#"Expanded Translations1" = Table.ExpandRecordColumn(#"Expanded Translations", "Translations", {"translations"}, {"Translations.translations"}),
#"Expanded Translations.translations" = Table.ExpandListColumn(#"Expanded Translations1", "Translations.translations"),
#"Expanded Translations.translations1" = Table.ExpandRecordColumn(#"Expanded Translations.translations", "Translations.translations", {"text", "to"}, {"Translations.translations.text", "Translations.translations.to"})

in
#"Expanded Translations.translations1"
 
When I invoke the column, I get the following error:
 
Expression.Error: We cannot convert a value of type List to type Table.
Details:
Value=[List]
Type=[Type]
 
However, if I comment from the line #"Expand Translations", replace "in" with translation, I am able to expand the rows until I get the proper values.
 
As a complement, when I post to the API with Postman, the format I am sending is:
 
[
{"text":"Test String 1"},
{"text":"Test String 2 "}
]
 
And the answer back is:
 
[
{
"translations": [
{
"text": "Sequência de teste 1",
"to": "pt"
}
]
},
{
"translations": [
{
"text": "Sequência de teste 2 ",
"to": "pt"
}
]
}
]
 
So, again, any help would be greatly appreciated. Thanks.
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    seems to work ok?

     

    let
    Source = Json.Document("[
{
""translations"": [
{
""text"": ""Sequência de teste 1"",
""to"": ""pt""
}
]
},
{
""translations"": [
{
""text"": ""Sequência de teste 2 "",
""to"": ""pt""
}
]
}
]"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"translations"}, {"translations"}),
    #"Expanded translations" = Table.ExpandListColumn(#"Expanded Column1", "translations"),
    #"Expanded translations1" = Table.ExpandRecordColumn(#"Expanded translations", "translations", {"text", "to"}, {"text", "to"})
in
    #"Expanded translations1"

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    seems to work ok?

     

    let
    Source = Json.Document("[
{
""translations"": [
{
""text"": ""Sequência de teste 1"",
""to"": ""pt""
}
]
},
{
""translations"": [
{
""text"": ""Sequência de teste 2 "",
""to"": ""pt""
}
]
}
]"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"translations"}, {"translations"}),
    #"Expanded translations" = Table.ExpandListColumn(#"Expanded Column1", "translations"),
    #"Expanded translations1" = Table.ExpandRecordColumn(#"Expanded translations", "translations", {"text", "to"}, {"text", "to"})
in
    #"Expanded translations1"
    • bottos's avatar
      bottos
      Frequent Visitor
      2 years ago

      Thank you. Now I see what I was doing wrong.