Forum Discussion
Transform content to JSON is not working
I have the following JSON content within one colum of my data set in Power BI Desktop:
{valueMap={3={adTitle=User's Attributes, userAttr={Company=xxxxxxxxxx}, fields=[XX_XXXXXX_FIELD_AD {ID = 1}]}}}
I want to extract the value from Company (i.e. xxxxxxxxxx). To achieve this I'm trying to convert the type of the column to JSON by using the "Transform -> JSON" option. However, when I do this I get an error message as the content of the cells:
DataFormat.Error: We found an invalid character between object key and value in JSON input.
Details:
Value=
Position=9
Also, for the cells that had a value null, the error is:
Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type.
How can I successfully convert this column to JSON? Is there a better way to extract the value I need from the JSON string?
Thanks
If all your JSON content have the same pattern, you can also try the following way to extract the Company information.
In Power Query Editor, Select the JSON column and then choose add Column From Examples:
In the first row of new colum, fill in the Company vaule you want to extract, then Power BI should automatically fill in the other vaules.
3 Replies
hi, cesarvinas
You may try to use Add Column->Extract->Text Between Delimiters
Result:
Best Regards,
Lin
Thank you, Lin!! This was also the answer. I've accepted Jessica's one though because I think using the the "column from examples" feature makes it simpler for other use cases too.
If all your JSON content have the same pattern, you can also try the following way to extract the Company information.
In Power Query Editor, Select the JSON column and then choose add Column From Examples:
In the first row of new colum, fill in the Company vaule you want to extract, then Power BI should automatically fill in the other vaules.
