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

[vibee1208]

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)

[v-jingzhang]

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/6193... , 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.

[Vijay_A_Verma]

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

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