cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cesarvinas Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Jessica_Seiya Established Member
Established Member

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

3 REPLIES 3
Community Support Team
Community Support Team

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
Jessica_Seiya Established Member
Established Member

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

cesarvinas Frequent Visitor
Frequent Visitor

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 132 members 1,671 guests
Please welcome our newest community members: