Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
cesarvinas
Advocate I
Advocate I

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

 

 

v-lili6-msft
Community Support
Community Support

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.