Error in JSON InputFile

[Anonymous]

Hi All,

I have an issue with a JSON file I import from an API call.

But it gives me the following error : 

I have noticed that the JSON contains some unsupported "Ascii" types, whats makes it return this error. 
Is there any way to convert these values to text, BEFORE/WHILE loaded into Power BI? 
I know the function Text.FromBinary, but don't think it will help me out in this case.

let
    Source = Json.Document(Web.Contents("https://XXX.service-now.com/api/now/table/change_request")),
    #"Converted to Table" = Table.FromRecords({Source}),
    #"Expanded result" = Table.ExpandListColumn(#"Converted to Table", "result"),

Will this only be possible through adjusting my API ? 

@ImkeF , I know you are the MQuery legend here, any idea? 

regards,

Robbe

[ImkeF]

Hi @Anonymous ,

it would be: 

let
    Source= Text.FromBinary(Web.Contents("https://XXX.service-now.com/api/now/table/change_request"))

But be warned: This is going to be a rabbithole!!

[ImkeF]

Hi @Anonymous ,

yes I think that Text.FromBinary is your only option here.

If your JSON is not valid, you have to adjust it to be properly detected from PowerBI.

Maybe you just have to eliminate the last closing bracket ")".
This could be achieved by Text.BeforeDelimiter with the option to start from the end of the string.

[Anonymous]

Hi @ImkeF ,

Cant seem to find a solution to this.
How should I catch the error? 

It's also hard to troubleshoot as I get no errors in the PQuery preview.

How should Implement Text.FromBinary in here ? 

Regards,

Robbe