JSON API | Expression.Error: We cannot convert a value of type Record to type List.

[bushead1979]

Hi,

I'm trying to import some api data into powerbi and get the error;

Expression.Error: We cannot convert a value of type Record to type List.

Details:
Value=Record
Type=Type

The JSON extract from the api looks like this below and I can individually extract one record but not the whole model.  I'm new to JSON and I'm confused how I am supposed to convert the whole dataset to a table?

[
{
"configuration_items" : [],
"new_attachments" : [],
"new_logs" : [],
"summary" : "123",
"last_update_at" : "2017-11-06 21:56:08+00",
"case_type" : "Request for Information",
"ticket_number" : "TK-QA-00014999",
"created_at" : "2017-11-06 21:56:08+00",
"priority" : "PRI-333",
"category" : "CTI-QA-00000016",
"ticket_state" : "Open - Needs Attention",
"external_ref" : "INC"
},

[
{
"configuration_items" : [],
"new_attachments" : [],
"new_logs" : [],
"summary" : "123",
"last_update_at" : "2017-11-06 21:56:08+00",
"case_type" : "Request for Information",
"ticket_number" : "TK-QA-00000009",
"created_at" : "2017-11-06 21:56:08+00",
"priority" : "PRI-333",
"category" : "CTI-QA-00000016",
"ticket_state" : "Open - Needs Attention",
"external_ref" : "INC"
}

]

[fcarstens]

I am experiencing a similar problem.

Here's my code:

= let
url = Json.Document(Web.Contents("https://example.com")),
body = "{ ""Parameter1"": true, ""Parameter2"": false, ""FromDate"": ""2019-04-21"", ""ToDate"": ""2019-04-27""}",

Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))
in
Source

And the error I'm getting:

  Expression.Error: We cannot convert a value of type Record to type Text.
  Details:
  Value=Record
  Type=Type

 The json result looks like this:

{
"TotalResults": 2,
"ReturnedResults": 2,
"Results": [
{
"ID": 1234567,
"ItemCode": "ASDF",
"ItemDescription": "ASDF Product",
"Quantity": 99.0000,
"TotalSelling": 9999.00,
},
{
"ID": 1234568,
"ItemCode": "QWER",
"ItemDescription": "QWER Product",
"Quantity": 99.0000,
"TotalSelling": 9999.00,
}
]
}

 Adding [Results] to the end of the "Source = " line didn't work.  Maybe I'm doing it wrong?:

Source = Json.Document(Web.Contents(url,[Headers = [#"Content-Type"="application/json"], Content = Text.ToBinary(body) ] ))[Results]

[Anonymous]

@bushead1979,

Could you please post the code in your Advanced Editor? What steps in Query Editor do you add when you get this error?

Regards,

Lydia

[bushead1979]

Being new to the query editor I can't figure out what I need to amend to get the source into a table with seperate columns.

let
Source = Json.Document(Web.Contents("https://url...")),
Table = Table.FromRows(Source)
in
Source

I have managed to get a single record but not the whole record set I'm assuming I need iterate through the records in a for loop or something?

[Phoenixke]

@bushead1979: You're missing an array bracket in the example respons file that you posted.

Any chance you can post an actual response? (omitting any personal info of course). It should start with "{ "something" : [".

You should then try Json.Document(Web.Contents("https://url..."))[something]