Parsing JSON from Snowflake Datamart

[AdolphHernick]

Hello,

We are currently trying to parse some JSON data from a Snowflake Datamart inside Powerbi. The JSON is in a column that we bring in from Snowflake. We are importing the tables into Power BI. There is one column that is formatted in valid JSON with information we want to parse out into separate columns.

When we go to transform the data and select JSON for the transformation it parses out into columns for us inside our Transform window. However, when we go to apply the changes we get the following error.

"OLE DB or ODBC error: [DataFormat.Error] We found an unexpected character in the JSON input.."

We have taken the JSON and ran it through a validator and confirmed it is valid JSON. We have also created a separate column and converted the column to a String and tried again. This still produced the same error. 

Does anyone have any advice on how we can parse out JSON that is coming in from a datamart inside Power BI?

Thank you in advance for any advice or suggestions anyone can provide!

Thank you,

[v-yueyunzh-msft · Accepted solution]

Hi , @AdolphHernick 

According to your description,  you get the data from Snowflake Datamart  and one column is the json , when you parse the json you encounter the "OLE DB or ODBC error: [DataFormat.Error] We found an unexpected character in the JSON input..".

And you  have taken the JSON and ran it through a validator and confirmed it is valid JSON.

First of all, according to my understanding, processing JSON data in Power Query should have little to do with the data source, and according to your error message, the error message indicates that there are some characters in this JSON that make it impossible to recognize.

For this problem, we can take the json string in your data source, copy it in a .json file, and then use Power BI's json connector to connect to see if there will be an error condition to test.

In addition, we generally use the Json.Document function to return the content of the JSON document.

For more information, you can refer to :
Json.Document - PowerQuery M | Microsoft Learn

Parse text as JSON or XML - Power Query | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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