Reply
Frequent Visitor
Posts: 2
Registered: ‎10-14-2018
Accepted Solution

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


Accepted Solutions
Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Transform content to JSON is not working

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:

2018-10-16_17-40-26.png

 

 

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.

2018-10-16_17-43-00.png

 

 

 

View solution in original post


All Replies
Community Support Team
Posts: 1,317
Registered: ‎07-30-2018

Re: Transform content to JSON is not working

hi, @cesarvinas

You may try to use Add Column->Extract->Text Between Delimiters 

4.PNG

5.PNG

 

Result:

6.PNG

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Established Member
Posts: 161
Registered: ‎01-02-2018

Re: Transform content to JSON is not working

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:

2018-10-16_17-40-26.png

 

 

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.

2018-10-16_17-43-00.png

 

 

 

Frequent Visitor
Posts: 2
Registered: ‎10-14-2018

Re: Transform content to JSON is not working

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.