We found extra characters at the end of the JSON input - Get Data from Azure devOps

[Fsrodrigues]

Dear all,

I have a query that is working on Power BI Desktop, but when a publish it and try to refresh it, I am facing the error bellow.

DataFormat.Error: <ccon>We found extra characters at the end of the JSON input.. Value = <. Position = 4. </ccon>. The exception was raised by the IDbCommand interface. 

The query is:

( testPlanId as text) =>

let
NextResult = (testPlanId as text, token) =>
let

relativePath = "/Org/Proj/_apis/testplan/Plans/" & testPlanId & "/suites?expand=children?api-version=7.0",

relativePathWithContinuation =
if token = null then
relativePath
else
relativePath & "&continuationToken=" & Number.ToText(token) & ";0",

Content = Web.Contents(TFSUrl,[RelativePath=relativePathWithContinuation]),
AsJson = Json.Document(Content)[value],
continuationToken = ReturnLastId(Content),
//continuationToken = Value.Metadata(Content)[Headers][#"x-ms-continuationtoken"]?,
Result =
if continuationToken = null then
AsJson
else
AsJson & @NextResult(testPlanId,continuationToken)
in
Result,

GetTestSuitesForPlan= NextResult(testPlanId,null)

in
GetTestSuitesForPlan

Can anyone help, please?

Is there an aditional string I have to add in the query so it will work in Power BI Service as good as it works  in desktop?

[v-yueyunzh-msft]

Hi , @Fsrodrigues 

According to my search and research on the error you provided, if your output is a multi-dimensional JSON file, you can try adding [ at the beginning, ] at the end, and after every line, and for other possibilities of this problem, you can also refer to these related cases:

Solved: "We found extra characters at the end of JSON inpu... - Power Platform Community (microsoft....

Get authorization code in JSON format - Microsoft Power BI Community

Solved: We found extra characters at the end of the JSON i... - Microsoft Power BI Community

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Fsrodrigues]

Hi v-yueyunzh-msft,

Thank you for your response.

I don't understand your point with 'adding [ at the beginning, ] at the end, and after every line', could you please explain me?

[mharms]

Hi Fsrodrigues,

I know it's an old thread, but did you find a solution here?

Because I'm running in the same problem and the web is not really helpfull.

Best regards!