Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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!!
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.