DataSource.Error: OData: Invalid JSON. A comma character ',' was expected in scope 'Array'. Every two elements in an array and properties of an object must be separated by commas

When downloading data from (cloud) platforms using OData, an error may be shown by Power BI (or Power Query) due to invalid JSON being retrieved:

DataSource.Error: OData: Invalid JSON. A comma character ‘,’ was expected in scope ‘Array’. Every two elements in an array and properties of an object must be separated by commas.

This error message indicates that the data retrieved using the JSON transport format is actually not in valid JSON format.

This topic provides you means to analyze this error by providing background and tools. Probably, not all possible causes are documented. Please add your problem as a new topic so when this topic does not provide you sufficient help. Include the URL of this topic in your new topic; Invantive and maybe other members of the community will try to assist you.

JSON and OData

The data downloaded is packaged in the so-called JSON format. JSON is a text representation and is reasonably human-readable. The OData format is an extension to JSON, which provides more context, but is still readable.

For use with Power BI, Power Query, Dataflows and Azure Data Factory Invantive Bridge Online heavily leans on OData version 4 since Microsoft provides an out-of-the-box provider. Additionally, the Microsoft OData driver offers good performance and scalability, including forwarding many filter types to the Invantive data sources to reduce network use.

Retrieve OData in JSON format

First learn what JSON looks like and how to retrieve the JSON payload of the OData Bridge of Invantive Cloud using solely your browser.

Execute the following steps to download and show the JSON in your browser:

Error message

The error message above indicates that the JSON has an invalid structure. There are two common causes of the error “A comma character ‘,’ was expected”:

  • An error occurred during downloading the data.
  • Invantive Bridge Online returns invalid JSON.

Error during downloading the data

The JSON can be invalid since Invantive Bridge Online starts returning data to Power BI before all data has been processed. This approach is called “streaming” as opposed to “batching”, and considerably improves time to first row received and often also improves total download time.

A disadvantage, especially with environments with hundreds of companies, is that some company’s data will not be processed early. When such a company has a setup error or even corrupt data, an error will be triggered.

This error is also inserted into the JSON to help you learn what is going wrong. The error is also registered in System Messages of Invantive Cloud.

The serialized version of the error might resemble something like:

{ 
  "error": 
  { "code": "itgensop057"
  , "message": "Server was unable to process request. ---\u003E Geen bedrijf rooster Check with the custom error message instructions of the platform used. (itgensop057, 673216ae-1e63-4245-937b-ce436d2ba0e5)"
  , "target": null
  , "details": 
    [ 
      { "code": "673216ae-1e63-4245-937b-ce436d2ba0e5"
      , "target": null
      , "message": "Unique ID" 
      } 
    ]
  , "innererror": null 
  }
}

The error code itgensop057 is an essential piece of information to find and resolve the root cause.

Recommend approach to find the actual cause is to download the JSON manually in a browser as described or check the contents of the System Messages form. When the error message is unclear, it is recommended to add a topic to the Dutch or English questions category on the forums, including the error message code.

As an alternative, a query on the same table can be run using Invantive Query Tool or similar products such as the online SQL editor on Invantive Cloud.

Invantive Bridge Online returns invalid JSON

The known samples of the second category (invalid JSON returned by Invantive Bridge Online) are:

Deze melding (in Excel) komt ook van een totaal ander issue. Bij gebruik in een query van <> (is niet gelijk aan) wordt deze fout blijkbaar ook geretourneerd.

Voorbeeld oud:

= Table.SelectRows(#“filter methode”, each [Status] <> 50)** *(werkte t/m 10-04-2021)

Nieuw:

= Table.SelectRows(#“filter methode”, each [Status] < 50 or [Status] > 50)