Can I extract the error message as a text string?

[jmillsjmills]

I'm getting an error message that when I click into it, it says:

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

Details:
Value=;
Position=805732

I would like to convert this error message from being along the yellow bar to being text in a table with one row and one column. That's all I need to know how to do - do you have any ideas please?

Thanks!

For what it's worth, it's because this number in my error message can also be my solution to the error. If I can extract 805732 - a number that will change each time I run the query - I can dynamically handle this error. If I extract "805732" and convert it to a number, I can then change the original JSON input using the Text.Start function (trimming off the extra characters that cause the error).

So all I need to do is take the error message and have it returned in the table, so I can manipulate the output.

[Anonymous · Accepted solution]

Hi @jmillsjmills ,

Here's a blog about how to extract the error message in Power Query, you could have a try.

https://www.sumproduct.com/blog/article/power-query-blogs/power-query-trying-to-extract-errors

Use the try function to extract error message. For example,

add a custom column with try function.

You'll get a column with records. Expand the error.

Continue to expand the message.

You can expand what you want according to your needs.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmillsjmills]

Thanks so much!

[Anonymous]

Check out Ben Gribaudo's article here:

https://bengribaudo.com/blog/2022/02/21/6561/enhancing-an-errors-detail#more-6561 

--Nate