Service Now API Invalid Character between object key and value

[Ruairi_CloudP]

Hello All, 

We're currently trying to get data from a ServiceNow API endpoint, however we keep running into the following error message:

Details: "We found an invalid character between object key and value in the JSON input."

I've seen this article linked to some other posts which seems to be running into the same issue: https://community.powerbi.com/t5/Desktop/Expression-Error-There-weren-t-enough-elements-in-the/m-p/4...

However it hasn't really helped with the issue we're having at this is happening at the source before anything is loaded into Power BI, 

Any help with this would be appreciated!

[DonBates]

Recently ran into this issue. JSON payload met RFC standards and could be validated in other tools.

Resolution was upgrading from the v110 (November 2022) to v114 of Power BI.

No idea what other versions of Power BI were affected.

[joserenato-nova]

😱mine is v114 and is throwing this same error over and over 
Anyone any other idea???

[DonBates]

Just to verify it's definitely the "We found an invalid character between object key and value in the JSON input."?
Are you running table API or a custom Scripted REST API?

With API Explorer it was definitely a valid JSON payload?
Once I had validated it was a valid JSON payload I did play around with ensuring data was trimmed, but with the specific message we're getting here, none of that would have resolved the issue. There was no invalid characters, just a non-printable ASCII 20 space between name-value pair. 
I suppose there could be some quotation marks in your value that isn't properly escaped by ServiceNow, but seems odd. If you're running a Scripted REST API then start limiting the number of records returned, and make sure they have a specific order, to make your troubleshooting easier. Start with only returning a single specific record.

I had loaded BI fresh on another computer, but the gentleman that originally reported the issue really did just upgrade his BI in place.

[joserenato-nova]

It just worked 
An update from yesterday to Version: 2.115.663.0 64-bit (March 2023)
All blue sky again 

😉

[bdarcy]

Having the same issue. It sometimes works then sometimes fails with this error message. 

[tasa]

Hi @bdarcy , did you ever solved the ServiceNow data load issue "We found an invalid character between object key and value in the JSON input."?

I am facing the same. Sometimes it works, sometimes it doesn't. Very anoying 😒

[bdarcy]

Hi @tasa , we ended up doing a workaround by splitting the single dataflow into multiple. Basically having a couple source queries (ie: using the sysparam filter to divvy up the data which seemed to help) then combining into a final staging dataflow.

Since we only had Pro version of Power BI and could not use incremental load, this helped alleviate the volume of data being returned and the risk of it "falling in on itself".

May not be the most elegant solution but it seemed to work

[tasa]

Hi @bdarcy , thanks a lot for your reply! I am glad you mentioned this because this is exctly what I had to do as well, splid the data extraction from ServiceNow by multiple queries by YEAR and then combine them at the end. Anyways, I agree with you, not the most elegant solutions, but it worked at the end 😊. Thanks a for your time!

[antonstorozhuk]

Hi @Ruairi_CloudP 

I'm Anton from Alpha Serve.

I’d like to propose an alternative solution for you, you might want to try out our app - Power BI Connector for ServiceNow:

• https://store.servicenow.com/sn_appstore_store.do#!/store/application/87a42573879e0110fb5033773cbb35...
• https://www.alphaservesp.com/products/servicenow/power-bi/

We have a handy User and Admin Guides for your convenience, but feel free to reach out support team at support@alpha-serve.com if you need any assistance.

Thank you.

Regards,

Anton