We found extra characters at the end of JSON input.

[intrasight]

Getting this error when doing a "from the web"

Details: "We found extra characters at the end of JSON input."

When I send the JSON to a validator it says that it is fine. How can I get more specific details on why it is complaining? There is no further detail in the trace file.

[skaranam]

I dont have exact solution for this but i faced the same issue. Then i emailed to the team where the JSON script is getting generated and they fixed it on their side. 

[sherinmirza]

Thank you for your response.

I have enabled continuous export of Application Insights data into Azure Blob Storage which is completely managed by Microsoft. So I have no control over getting the JSON script fixed. Is there something we could do while importing blobs and combining files and parsing JSON?

[yssant]

does this have a solution?

[DolEgon22]

This is not unique to Power BI, however—it appears that this is also reproduced with Excel connecting to the blobs created from stream analytics. It appears that while other services can parse the JSON from ASA, PBI and Power Query cannot.

My troubleshooting steps:

• Using a 2 line sample from one of the JSON files, I run it through a parser and get parser errors (expecting 'EOF')
• When I wrap the JSON in square brackets and separate the lines with a comma, it parses fine
• I downloaded one of the JSON files and made 2 copies of it. I then edited one to have the brackets and comma delimiter and left the other one alone. I was unable to load the unedited JSON into PBI, but had no problem with the one I edited.

This appears to be an issue with how Power Query parses JSON from ASA. 

[DolEgon22]

Just a side note--my steps above don't solve our problem of connecting to Azure Blob Storage using PBI and automatically parsing the JSON stored there... my steps simply reveal the issue in PBI/Power Query. 

[sasmpu00]

The same issue is here. the same url output (KSON) saved in text and is working bit while directly placing , then not working. 

[Basjuh84]

Still no definitive solution for this problem? I want to read multiple JSON files from a folder. Individual JSON files work fine, but reading them all in from a folder gives me the same error!

[BNajlis]

Found the same issue when trying to load a JSONL file (http://jsonlines.org/). To solve it, I ended up importing it as a text file (which takes one record per line) and then using the Parse function in PowerQuery to parse the JSON in each line.

[CLTJSDUDE82]

how do you import as text from a site?

I have the below codes:

let
url = "https://ws.appointment-plus.com/Appointments/" & "GetAppointments",
headers = [#"Authorization" = "Basic Secretcode", #"Content-Type" = "application/json"],
postData = Json.FromValue([x = 235.7, y = 41.53]),
response = Web.Contents(
url,
[
Headers = headers,
Content = postData
]
),
jsonResponse = Json.Document(response)
in
jsonResponse

[Anonymous]

This solution worked for me thx.

[bitaria]

Was there a definitive answer to this question? I'm trying to load data from a .json file, and while all editors I can think of have no issues with it Power BI throws the error. Is there a log file somewhere that would point to what is causing this error, other than referencing the "extra character"?

[Evogelpohl]

I'm using Azure Stream Analytics to take IoT data and send it to Azure blog storage as a JSON file. 

When I attempt to open the JSON file from within PowerBi Desktop (azure blob), i get this same error.

If i download the JSON file to my desktop and open it from there, i get the same error.

Solution?

[yssant]

hi, get yourself repair this? i have same error 

[Anonymous]

I have exactly the same issue with ASA to blob storage and then not being able to consume the information with PowerBI.

What is causing this and when it will be fixed?

Thank you

[jPinhao]

I've had a similar issue before. In our case, the input was actually JSONL (json lines or something), and it was basically missing start and end braces around the full json content, as well as commas at the end of each line, to parse it as an array of json objects. Not sure if you're problem is the same, but it might be something along the same lines -> valid json, just not wrapped together properly.

Hope that leads you in the right direction 🙂

[rmh]

Thansk for the tip, and by the way, braces = Square braces

[intrasight]

Nope, it's regular JSON. And it validates with every tool that I've thrown it at. Does Power BI have some "special" JSON needs?

[intrasight]

I noticed that the section of JSON containing the data table had no line breaks between rows. After adding line breaks, I no longer get the above error message.  This leads me to believe that a) there is some limitation in the JSON parser used by Power BI, and b) this limitation manifests itself with an incorrect error message.

[menris]

I am seeing the same error and the json being returned by the API is valid. How did you add the line breaks?

[intrasight]

I would download the JSON from that API and then serve it from your own web app to figure out what are the needed changes. Then make your web app a proxy to the API and make those changes on the fly to the response.

[menris]

This issue comes when we try to refresh data after uploading the pbix. When we connect to the rest API's in Power BI it works fine there.