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

[bottos]

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.

[lbendlin · Accepted solution]

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]

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