DataFormat.Error: We found extra characters at the end of JSON input.

[LexBolkesteijn]

Hello all,

Been working around for a few days with PowerBI now and got a lot of stuff working allready.  Have used variouse Visualzations and connecting to large volume SQL Server, Azure SQL databases works like a charm.

But when I import a large JSON file with data I got a error. The file is a 49Mb  JSON file with 63774 records and when I loaded it I got this error : 

DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=
Position=1681799

So I took a small snip of the file to setup the datasource in the query editor. And after a few tests and making the dasboard it was time to work with the real file.  I found out that the when I'm below this value it does work (output.json size 1.681.786  ).

So my questions are :

1. is there a size limit to a JSON file ? 
2. is it a know issue / bug (I have look on variouse places but can't find any refference to this).

[Jmenas]

Hi all, 
I had the same issue but I was making the called with the function and from a different query. The issue is to parse the call  before it gets the JSON, The other way is to have the JSON open as text and parse it using a technique mention in a post from ExcelGuru: https://www.excelguru.ca/blog/2015/11/19/keep-only-numbers-in-power-query/ 

They remove nothing but numbers I change to nothing but Special characters, you can add more if you want to here is my code for the list that you load before doing any changes. 

CharsToRemove = List.Transform({33..45,47}, each Character.FromNumber(_))

Then, you can add a custom column: 

"Column name"
Text.Remove([Call elementclean],CharsToRemove)

This case not sure if it works from Azure SQL like this but you should try it in SQL. 

Hope this help someone. 

Best,
J

[mtzvictor]

I'm also looking for and answer to this. 

[LexBolkesteijn]

Tested it today also for Azure blob storage. Same problem.

[DolEgon22]

Four months later and nobody has responded to this. We're running into the exact same issue when using Azure Blob Storage as a datasource (even CSV). 

DataMashup.Trace Warning: 24579 : {"Start":"2017-08-14T19:17:20.5789351Z","Action":"SimpleDocumentEvaluator/GetResult","HostProcessId":"17348","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataFormat.Error] We found extra characters at the end of JSON input.\r\nStackTrace:\n   at Microsoft.Mashup.Engine1.Library.Json.JsonParser.Parse(JsonTokenizer tokenizer)\r\n   at Microsoft.Mashup.Engine1.Library.Json.JsonModule.Json.DocumentFunctionValue.TypedInvoke(Value jsonText, Value encoding)\r\n   at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2`3.Invoke(Value arg0, Value arg1)\r\n   at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n   at Microsoft.Mashup.Engine1.Language.FunctionInvocationExpression2.Execute(MembersFrame1& frame)\r\n   at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n   at Microsoft.Mashup.Engine1.Language.ExpressionInvocationExpression2.Execute(MembersFrame1& frame)\r\n   at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(MembersFrame1& frame)\r\n   at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Runtime.Library.Linker.BindFunctionValue.TypedInvoke(RecordValue environment, Value section, TextValue name)\r\n   at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue3`4.Invoke(Value arg0, Value arg1, Value arg2)\r\n   at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n   at Microsoft.Mashup.Engine1.Language.ListExpression.RuntimeListValue.Force(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.ListExpression.RuntimeListValue.get_Item(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.FunctionInvocationExpression2.Execute(Value frame)\r\n   at Microsoft.Mashup.Engine1.Language.Expression.ExecuteCondition(Value frame)\r\n   at Microsoft.Mashup.Engine1.Language.DebugExpression.ExecuteCondition(Value frame)\r\n   at Microsoft.Mashup.Engine1.Language.IfExpression.Execute(Value frame)\r\n   at Microsoft.Mashup.Engine1.Language.DebugExpression.Execute(Value frame)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.Force(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.RecordExpression.RuntimeRecordValue.get_Item(Int32 index)\r\n   at Microsoft.Mashup.Engine1.Language.MembersFunctionValue0.Invoke()\r\n   at Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.BeginGetResult(DocumentEvaluationParameters parameters, Action`1 callback)\r\n\r\n\r\n","ProductVersion":"2.49.4831.521 (PBIDesktop)","ActivityId":"e683962d-0763-4d48-978f-e63be7e527c8","Process":"Microsoft.Mashup.Container.NetFX40","Pid":12636,"Tid":1,"Duration":"00:00:00.0200926"}

[Anonymous]

Hi everyone!

I modified the JIRA content pack and I'm having the same error when I upload to online.
Is there any news about this error?

Thanks!