Forum Discussion
GA4 Connector issue, JSON unexpected Character
Hi, Any help would be appreciated, everything was fine until it wasnt... I added no new measures or edits of any kind, then the following error ocurred. I have tried following suggestions from answers to similar issues on here, with no success.
Hi, PAS_PBI
The reason for your error may be that the API returns an HTML error page instead of JSON, or that it reads an XML/HTML file instead of a JSON file, or that the database field contains text that is not in JSON format.
Power Query checks the raw response examplelet source = Web.Contents("https://api.example.com"), rawText = Text.FromBinary(source), //First, check if the HTML tags are included isHtml = Text.Contains(rawText, "<!DOCTYPE html>") in if isHtml then error "API Return HTML Error" else Json.Document(source)You can also verify the response format by calling the API directly through Postman. Check whether the HTTP request header contains Accept: application/json. Add API error handling:
try { var response = await client. GetAsync(url); response. EnsureSuccessStatusCode(); //Automatically throws HTTP errors var json = await response. Content.ReadAsStringAsync(); } catch (HttpRequestException e) { //Catch error codes like 429/500 }You also need to check if your JSON conforms to the standard specification, you can check the following documentation:
In the meantime, you can verify your json file at the link below:
JSON Schema Validator - Newtonsoft
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi PAS_PBI I think the error is coming because of invalid or malformed JSON data could you check in your Advanced editor for any steps that introduced any error and if API contains nested JSON could you try power quersJSON parcing query function
If this post helped please do give a kudos and accept this as a solution
Thanks In AdvanceHi, PAS_PBI
The reason for your error may be that the API returns an HTML error page instead of JSON, or that it reads an XML/HTML file instead of a JSON file, or that the database field contains text that is not in JSON format.
Power Query checks the raw response examplelet source = Web.Contents("https://api.example.com"), rawText = Text.FromBinary(source), //First, check if the HTML tags are included isHtml = Text.Contains(rawText, "<!DOCTYPE html>") in if isHtml then error "API Return HTML Error" else Json.Document(source)You can also verify the response format by calling the API directly through Postman. Check whether the HTTP request header contains Accept: application/json. Add API error handling:
try { var response = await client. GetAsync(url); response. EnsureSuccessStatusCode(); //Automatically throws HTTP errors var json = await response. Content.ReadAsStringAsync(); } catch (HttpRequestException e) { //Catch error codes like 429/500 }You also need to check if your JSON conforms to the standard specification, you can check the following documentation:
In the meantime, you can verify your json file at the link below:
JSON Schema Validator - Newtonsoft
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
