Forum Discussion
JSON Transformation Error
Hi all. I am getting data from a Sharepoint Online List. It has several fields and one is in JSON format. When I transform it I can expand it. I click to expand, then again and expand to new rows. I get three columns named label, reason code, and notes from the example below.
| {"items":[{"label":"A-1","reasonCode":"CLAIM OK-NO ISSUES","notes":"but return in SC for non repair"}]} |
When I close and apply I get the following error. "OLE DB or ODBC error: [Expression.Error] The parameter is expected to be of type Text.Text or Binary.Type.
The columns come up in ABC123 format when I expand. I even tried converting them to text. No luck. Thoughts on what could be causing the issue?
I found the solution. I brought the column in three times. Once for each component of the JSON. Then I parsed each column picking a different component. Worked like a charm. The issue seemed to be in going from one single column of JSON where there is more than one column on the output.
2 Replies
nywi6100,
Is there any possibility that you can export the JSON field to a flat JSON file so that I can test? Or you can use JSON connector in Power BI Desktop to connect to the exported JSON file and check if the error still occurs.
Regards,Lydia
I found the solution. I brought the column in three times. Once for each component of the JSON. Then I parsed each column picking a different component. Worked like a charm. The issue seemed to be in going from one single column of JSON where there is more than one column on the output.
