Forum Discussion

vibee1208's avatar
vibee1208
New Member
4 years ago

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

Here is my Power Query code:
let
  url = "url.com",
  body = "{""username"":""USERNAME"",""password"":""PASSWORD"",""createdFromDate"":""2022-05-15"",""createdToDate"":""2022-05-19"",""status"":""FIX"",""format"":""CSV"",""separator"":"",""}",
  headers = [#"Content-Type"="application/json"],
  
  Source = Json.Document(Web.Contents(url,[Content = Text.ToBinary(body),Headers = headers]))
in
  Source
 
Getting the ERROR:
DataFormat.Error: We found extra characters at the end of JSON input.
Details
Value =
Position = 0
 
Python Version of this is working but when trying to recreate in Power Query the Error above is present.
Python Code:
 

data = {

    "username": "USERNAME",

    "password": "PASSWORD",

    "createdFromDate": "2022-05-15",

    "createdToDate": "2022-05-19",

    "status": "FIX",

    "format": "CSV",

    "separator": ","

}

 

resp = requests.post(url="url.com", data=data, verify=False)

 

print(resp.content)

Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Change separator to anything other than comma say to a semi-colon. 

    Hence replace ""separator"":"",""}" with ""separator"":"";""}"

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

    Hi vibee1208 

     

    Your code looks good. You may also try below code. 

     

    let
  apiUrl = "https://xxxxxxxxxx",
  headers = [#"Content-Type" = "application/json"],
  body = [
    username = "USERNAME",
    password = "PASSWORD",
    createdFromDate = "2022-05-15",
    createdToDate = "2022-05-19",
    status = "FIX",
    format = "CSV",
    separator = ","
  ],
  Source = Json.Document(Web.Contents(apiUrl, [Headers = headers, Content = Text.ToBinary(Uri.BuildQueryString(body))]))
in
  Source

     

    If it still has the same error, you may refer to https://community.powerbi.com/t5/Desktop/We-found-extra-characters-at-the-end-of-JSON-input/m-p/61931 , try the solutions provided by other community users. It seems the way Power Query deals with JSON content is different from other tools.

     

    Or try the solution in Troubleshooting section in this doc https://docs.microsoft.com/power-query/connectors/json#troubleshooting 

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.