Get Data from WEB returning Details: "We found extra characters at the end of JSON input."

[haze]

Hi,

I am getting error on Unable to connect when I am tyring to Get Data from WEB (Basic URL: <Gerrit REST api url that I wanted to access to>).

And when I tried to validate the JSON format of my query, I noticed that there are some unexpected characters as shown below before the JSON content start.

)]}'
[
  [
    { <JSON CONTENT start here>

I am not sure whether is that the cause of why I am not able to get data from web successfully.

Would be great if anyone of you can teach me a way to get rid of the unwanted character so that I can use the get data from web feature.

Thanks. Looking forward for the recomendation.

Cheers.. Happy New Year in advance!~

[v-yuezhe-msft]

@haze,

Could you please download the JSON file from web , then use JSON entry in Power BI Desktop to connect to the file and check if you get the same error?

If there is no issue using this method, the error could  be caused by the inappropriate way to connect to the web URL, for example, you don't provide login information, for more details, please review the following similar thread.

https://social.technet.microsoft.com/Forums/office/en-US/88a02a77-4b3d-40cb-a97c-8bb33896167a/power-...

Regards,
Lydia

[haze]

Hi,

Thanks for your reply. 

I tried to get data from the JSON file that I downloaded from the web too. I am getting the same error message "Unable to connect"  Details: "We found extra characters at the end of JSON input.

Any idea?

[v-yuezhe-msft]

@haze,

Have you used online JSON format tools to validate your JSON file?

Regards,
Lydia

[haze]

Yes, I did. It is not in a valid JSON format due to the some unexpected characters as shown below before the real JSON content start.

)]}'
[
  [
    { <JSON CONTENT start here>

[v-yuezhe-msft]

@haze,

Regarding to this error, please check the discussion in  the following similar thread. And please share the JSON file so that I can test.

Regards,
Lydia

[haze]

Here you go with the JSON file.

)]}'
[
{
"id": "isg_es-sy_repo-a~master~Icb88a96142678a678562eb051eed9b4002b154ee",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "Icb88a96142678a678562eb051eed9b4002b154ee",
"subject": "Testing as reviewer",
"status": "NEW",
"created": "2017-10-12 05:54:52.473000000",
"updated": "2017-10-12 05:54:52.473000000",
"mergeable": true,
"insertions": 3,
"deletions": 1,
"_number": 18737,
"owner": {
"_account_id": 1000295
}
},
{
"id": "isg_es-sy_repo-a~master~I2c90d3229402a53eb5510fcfacd60bba5f4a495c",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I2c90d3229402a53eb5510fcfacd60bba5f4a495c",
"subject": "Testing for Siong Yee as reviewer",
"status": "NEW",
"created": "2017-10-12 05:52:25.797000000",
"updated": "2017-10-12 05:52:25.797000000",
"mergeable": true,
"insertions": 3,
"deletions": 1,
"_number": 18736,
"owner": {
"_account_id": 1006467
}
},
{
"id": "isg_es-sy_repo-a~master~I6c5a54c626bac6b2edea4905351f8ec456458567",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I6c5a54c626bac6b2edea4905351f8ec456458567",
"subject": "test",
"status": "NEW",
"created": "2017-10-12 05:47:12.519000000",
"updated": "2017-10-12 05:47:12.519000000",
"mergeable": false,
"insertions": 2,
"deletions": 1,
"_number": 18735,
"owner": {
"_account_id": 1000295
}
},
{
"id": "isg_es-sy_repo-a~master~I1e303fc074169531c939e29fe2d76b95e018c262",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I1e303fc074169531c939e29fe2d76b95e018c262",
"subject": "Testing",
"status": "NEW",
"created": "2017-10-12 05:43:21.776000000",
"updated": "2017-10-12 05:43:21.776000000",
"mergeable": false,
"insertions": 3,
"deletions": 1,
"_number": 18734,
"owner": {
"_account_id": 1006467
}
},
{
"id": "isg_es-sy_repo-a~master~Ic53892568c4dd31167cdc24f725676dec413a5d3",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "Ic53892568c4dd31167cdc24f725676dec413a5d3",
"subject": "Merge branch \u0027master\u0027 isg_es-sy_repo-a",
"status": "NEW",
"created": "2017-10-12 05:40:58.000000000",
"updated": "2017-10-12 05:40:58.000000000",
"mergeable": true,
"insertions": 0,
"deletions": 0,
"_number": 18733,
"owner": {
"_account_id": 1000295
}
},
{
"id": "isg_es-sy_repo-a~master~I8eb5f0f80ca7674f4eb1fb2d9eb0ef3c4f704b44",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I8eb5f0f80ca7674f4eb1fb2d9eb0ef3c4f704b44",
"subject": "fail to push- no sign-of-by",
"status": "NEW",
"created": "2017-10-12 05:08:23.163000000",
"updated": "2017-10-12 05:08:23.163000000",
"mergeable": true,
"insertions": 2,
"deletions": 1,
"_number": 18732,
"owner": {
"_account_id": 1000295
}
}
]

[v-yuezhe-msft · Accepted solution]

@haze,

Removing )]}' from your JSON file, I can successfully import data from the JSON file to Power BI Desktop. It seems that your web generates a invalid JSON file.

[
{
"id": "isg_es-sy_repo-a~master~Icb88a96142678a678562eb051eed9b4002b154ee",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "Icb88a96142678a678562eb051eed9b4002b154ee",
"subject": "Testing as reviewer",
"status": "NEW",
"created": "2017-10-12 05:54:52.473000000",
"updated": "2017-10-12 05:54:52.473000000",
"mergeable": true,
"insertions": 3,
"deletions": 1,
"_number": 18737,
"owner": {
"_account_id": 1000295
}
},
{
"id": "isg_es-sy_repo-a~master~I2c90d3229402a53eb5510fcfacd60bba5f4a495c",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I2c90d3229402a53eb5510fcfacd60bba5f4a495c",
"subject": "Testing for Siong Yee as reviewer",
"status": "NEW",
"created": "2017-10-12 05:52:25.797000000",
"updated": "2017-10-12 05:52:25.797000000",
"mergeable": true,
"insertions": 3,
"deletions": 1,
"_number": 18736,
"owner": {
"_account_id": 1006467
}
},
{
"id": "isg_es-sy_repo-a~master~I6c5a54c626bac6b2edea4905351f8ec456458567",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I6c5a54c626bac6b2edea4905351f8ec456458567",
"subject": "test",
"status": "NEW",
"created": "2017-10-12 05:47:12.519000000",
"updated": "2017-10-12 05:47:12.519000000",
"mergeable": false,
"insertions": 2,
"deletions": 1,
"_number": 18735,
"owner": {
"_account_id": 1000295
}
},
{
"id": "isg_es-sy_repo-a~master~I1e303fc074169531c939e29fe2d76b95e018c262",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I1e303fc074169531c939e29fe2d76b95e018c262",
"subject": "Testing",
"status": "NEW",
"created": "2017-10-12 05:43:21.776000000",
"updated": "2017-10-12 05:43:21.776000000",
"mergeable": false,
"insertions": 3,
"deletions": 1,
"_number": 18734,
"owner": {
"_account_id": 1006467
}
},
{
"id": "isg_es-sy_repo-a~master~Ic53892568c4dd31167cdc24f725676dec413a5d3",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "Ic53892568c4dd31167cdc24f725676dec413a5d3",
"subject": "Merge branch \u0027master\u0027 isg_es-sy_repo-a",
"status": "NEW",
"created": "2017-10-12 05:40:58.000000000",
"updated": "2017-10-12 05:40:58.000000000",
"mergeable": true,
"insertions": 0,
"deletions": 0,
"_number": 18733,
"owner": {
"_account_id": 1000295
}
},
{
"id": "isg_es-sy_repo-a~master~I8eb5f0f80ca7674f4eb1fb2d9eb0ef3c4f704b44",
"project": "isg_es-sy_repo-a",
"branch": "master",
"hashtags": [],
"change_id": "I8eb5f0f80ca7674f4eb1fb2d9eb0ef3c4f704b44",
"subject": "fail to push- no sign-of-by",
"status": "NEW",
"created": "2017-10-12 05:08:23.163000000",
"updated": "2017-10-12 05:08:23.163000000",
"mergeable": true,
"insertions": 2,
"deletions": 1,
"_number": 18732,
"owner": {
"_account_id": 1000295
}
}
]

Regards,

Lydia

[haze]

Hi Lydia,

Yes, I tried to remove the unwanted characters manually and reimport the data. It works perfectly but is there a way for me to manipulate it thru Power BI because I need to Get data from Web feature instead of from the JSON File.

[v-yuezhe-msft]

@haze，

I don't think that you can manipulate the JSON file in Power BI Desktop, you would need to download the JSON file from web and manually edit the JSON file.

Regards,
Lydia

[haze]

Hi Lydia,

Thanks for your reply. I wanted to use Power BI Desktop to automate the metrix/data extraction from the web server so even manually is the option but I will opt out for that.

I am currently looking into the "Advanced Editor" feature in edit queries to try out whether can I manipulate the JSON before load it to Power BI using power query.

Would be great if you have some ideas on how to do that in Advanced Editor.

Happy New Year!!

[v-yuezhe-msft]

@haze,

With Power query, you can determine if the JSON file is valid, but I don't think that you can use Power query to re-format the JSON file by automatically removing extra characters in specific places. 

Regards,
Lydia

[haze]

Alright, got it. Thanks Lydia!