cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
intrasight
Helper IV
Helper IV

We found extra characters at the end of JSON input.

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.

38 REPLIES 38
gpfox
Frequent Visitor

I had same problem. Then I deleted the optional encoding parameter (I had it set at 1200) within the Json.Document function. That solved the error.

 

Json.Document(jsonText as any, optional encoding as nullable number)

 

Hennadii
Helper IV
Helper IV

I faced the same error message in Excel - Power Query editor.
A reason for that issue was wrongly selected VPN. After switching to a correct one that message is gone and a query is executed successfylly.
Hope it may help to someone )

In my query, I don't select any VPN. Can you explain further? thanks

I didn't use any syntax in my query related to VPN. It is just a network setting.

In my case, a json data was coming from the web.  Here is a simplified version of my query:

let
    Url = "https://somesite.com/content.json", 
    RawData = Web.Contents(Url),
    Json  = Json.Document(RawData),
    Value = List.First(Record.ToList(Json))
    ConvertedToTable = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    ConvertedToTable

 

This query returns a table only for one VPN setting while for another one it returns the error: DataFormat.Error: We found extra characters at the end of JSON input.

 

It was difficult to identify the root of the issue as simple typing in browser url returns json even with connected to "wrong" VPN.

 

SubasriG
New Member

Very weird ......the same i am facing issue but this is all of a sudden . I am trying to call api from table in power bi and it was working fine like 2 weeks back and now all of sudden getting this issue of "extra characters found at the end of JSON" . Is it like any rules of JSON parser changed or anything ... Unfortunately couldnt find a solution for this ... 😞 ... Can anyone please help me with this if you have solved this issue ..... My query goes like 
 (Name1 as text,Name2 as text) =>
let
Source = Json.Document(Web.Contents("URL",[Headers=[Accept="Basic base64- PAT ]])),

#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table" {2}[Value],
#"Pivoted Column" = Table.Pivot(#"Converted to Table", List.Distinct(#"Converted to Table"[Name]), "Name", "Value")

in
#"Pivoted Column"

Please help me with this .....really struck....

scotttaft
Frequent Visitor

This might not help everyone but thought I'd share:

 

I wrapped my JSON code in brackets ([...]) and it worked.

I was getting this error and couldn't find a solution. My JSON was formatted like the following:

 

{"name":"value"...},

{"name":"value"...}

 

and PBI kept throwing this error. I just added brackets and it worked like the following:

 

[

{"name":"value"...},

{"name":"value"...}

]

AnalystPower
Helper I
Helper I

Are you still having an issue with this? I was able to find a solution. In my example my JSON code just needed to be reformatted from JSON lines to regular JSON format and PBI handled it just fine after.
mericok
Frequent Visitor

Hi, When I want to refresh my PowerBI query I getting following error message. Could you please help me?

Thanks.

 

1.JPG

Continued problem w/ IoT data streamed to Azure Blob store (then get "we found extra...") when attempting to combine JSON files in PowerBI.

 

Here's how I solved it.  Works fine for me now.

 

1) Don't use the Combine (double arrow that gives you the error) feature.  Instead, make a query that trains a pattern on one of your json files.  To do this, just make a new query against your source:

 - Pick the first Json file

- Click the binary

- You should see a file icon, right click and "convert to TXT"

- Now you should see a single column, with JSON data <- but smooshed all in one col.

- Convert that column to JSON (right click col, convert, JSON)

- Now you have one good file export pattern that you can use as a function for all your others. 

- Now, edit this query in the Advanced editor and include the 'let' statement that turns this query into a function (mine is below)

- Replace the example file name with the variable you created.

- Click OK and try it out - put a full path to your JSON file in and see if you can invoke it correctly.

- If it works -> now create another query and source all your JSON files.  Use the "add column" -> "invoke custom function" and fill in the name of your function and click the file path as the function's source.

- It will run your function over and over - for every file and append them.  

 

Here's my FunctionCode:

 

let
    FxFixJson = (jsonfilepathname as text) =>
let
    Source = AzureStorage.Blobs("https://MYBLOBSTOREACCT.blob.core.windows.net/"),
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Content", "Name"}, {"Data.Content", "Data.Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Data.Name] = jsonfilepathname)),
    #"Data Content" = #"Filtered Rows"{0}[Data.Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Data Content",null,null,1252)}),
    #"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"deviceId", "messageId", "temperature", "humidity", "EventProcessedUtcTime", "PartitionId", "EventEnqueuedUtcTime", "IoTHub"}, {"deviceId", "messageId", "temperature", "humidity", "EventProcessedUtcTime", "PartitionId", "EventEnqueuedUtcTime", "IoTHub"})
in
    #"Expanded Column1"
in
    FxFixJson

Thank you for your response.

Below example helped me to fix the error but it only worked for one record.

When  i tried to apply FxFixJson function i am confused with what the (jsonfilepathname as text) should be?

 

 

let
    Source = AzureStorage.Blobs("XXXX"),
    wrfm1 = Source{[Name="XXXX"]}[Data],
    #"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Content", "Folder Path", "Name"}, {"Data.Content", "Data.Folder Path", "Data.Name"}),
    #"Data Content" = #"Expanded Data"{0}[Data.Content],
    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Data Content",null,null,1252)}),
    #"Parsed JSON" = Table.TransformColumns(#"Imported Text",{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"basicException", "internal", "context"}, {"Column1.basicException", "Column1.internal", "Column1.context"}),
    #"Expanded Column1.internal" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.internal", {"data"}, {"internal.data"}),
    #"Expanded Column1.context" = Table.ExpandRecordColumn(#"Expanded Column1.internal", "Column1.context", {"application", "data", "device", "user", "session", "operation", "location", "custom"}, {"context.application", "context.data", "context.device", "context.user", "context.session", "context.operation", "context.location", "context.custom"})
in
    #"Expanded Column1.context"

Anonymous
Not applicable

Hi

Just wan tto emphasis for the developers to solv this

I get this issue independent of three sources of JSON files 

Must be something that can be fixed for everyone in the provided parsing of JSON in PBI

HendrixSpirit
Helper I
Helper I

I'm having same issue when loading JSON from folder. Doing the first time works ok, but when updating, it appears "We found extra characters at the end of JSON INPUT.

Please correct this old error from 2016

csiege
New Member

I was able to enter an advanced query to resolve my problem.

I had to replace carriage returns with commas and surround the entire thing with a bracket to form a valid json doc.

 

let
Source = DataLake.Contents("https://SOMETHINGHERE.azuredatalakestore.net"),
TwitterData = Source{[Name="TwitterData"]}[Content],
#"TwitterStream-Output_0_9999 json" = Text.Replace(Text.Combine({"[", Text.FromBinary(TwitterData{[Name="TwitterStream-Output_0_9999.json"]}[Content]), "]" }), "#(cr)", ","),
#"Imported JSON" = Json.Document(#"TwitterStream-Output_0_9999 json",1252),
#"Imported JSON1" = #"Imported JSON"{0}
in
#"Imported JSON1"

yssant
New Member

does this have a solution? i have same error

skaranam
Frequent Visitor

Does anyone have solution to this.

 

 

Anonymous
Not applicable

I'm stuck with the same problem. I'm trying to build a Power BI custom connector, and I'm not even sure that the request by Web.Contents is executed, since I've tried inserting the json as a string in the Power Query and that works, but if I place the same string as a json-file on App Service or as Blob it fails. I'm stranded with with an option on how to trace/debug what really happens.

In my case it's working beautifully in Power BI Desktop, but for some reason I can't get it to run in the Visual Studio for the custom connector project.

 

Any ideas and feedback is most welcome as I expect this to be a simpel error or misconfiguration on my side, more than an error in the SDK.

 

Brgds Brian

I am getting the same error while importing Azure BLOB files (Created by Application Insights and stored in Azure Blob Storage) into powerbi and transforming JSON. Please help!

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. 

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
New Member

does this have a solution?

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors