Forum Discussion

hmeltonPSL's avatar
hmeltonPSL
Icon for Helper II rankHelper II
5 years ago

We found extra characters at the end of JSON input

I am trying to write the following query so I can include project description from azure devops in my powerbi report.  The URL works fine on its own and get JSON back..howver when I put it in the following function in POwerBI I get the error below.  

 

Any ideas?  thanks

 

= (projectid as text) as binary =>
let
Source = Json.Document(Web.Contents("https://dev.azure.com/ORGNAME/_apis/projects/" & projectid & "?api-version=6.0"))
in
Source

 

 

An error occurred in the ‘’ query. DataFormat.Error: We found extra characters at the end of JSON input.
Details:
Value=
Position=4

Need Help

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi hmeltonPSL ,

    nothing obvious comes to my mind here. 

    I would debug as follows:

    1) remove the "as binary" from the function:

     

    (projectid as text)  =>
let
Source = Json.Document(Web.Contents("https://dev.azure.com/ORGNAME/_apis/projects/" & projectid & "?api-version=6.0"))
in
Source

     


    2) if 1) doesn't help, then run the function without Json - conversion:

     

     

    (projectid as text)  =>
let
Source = Web.Contents("https://dev.azure.com/ORGNAME/_apis/projects/" & projectid & "?api-version=6.0")
in
Source

     

    You can then parse the result in a separate step/column with the "Text.FromBinary"-function and try to figure out what's wrong with the JSON.

     

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi hmeltonPSL 

     

    Since "The URL works fine on its own and get JSON back", you can try using Web connector in Power Query Editor to connect to this URL and test whether it can get the result back. You will need to enter credentials before connecting successfully. Power Query will detect the format of the data it gets and convert it to JSON format automatically. 

     

    If you can get correct result from the URL through Web connector, then you can use a query paramter to replace the projectid part in the URL. At last, in Queries pane, right click on the query and select option Create Function. This method is to use UI to create a function. You can compare its M codes and your codes to see which the difference is that may cause the error.

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it. Kudos are also appreciated. Thanks!