json - Is it possible to insert text in a column conditionally in Power BI?

Question

I am working on a project where I am pulling data from Mongo DB. I have a JSON source which is missing double quotes(") at some places.

[{_id:00000000000000000000000000000000,Action:"FeedbackGiven",Date:2020-12-10 18:17:32.792},
{_id:00000000000000000000000000000000,Action:"FeedbackGiven",Date:2020-12-10 19:02:24.604},
{_id:00000000000000000000000000000000,Action:"FeedbackGiven",Date:2020-12-10 19:16:16.615}]

I want to add double quotes(") for _id & Date values, so my data should look like

[{_id:"00000000000000000000000000000000",Action:"FeedbackGiven",Date:"2020-12-10 18:17:32.792"},
{_id:"00000000000000000000000000000000",Action:"FeedbackGiven",Date:"2020-12-10 19:02:24.604"},
{_id:"00000000000000000000000000000000",Action:"FeedbackGiven",Date:"2020-12-10 19:16:16.615"}]

Without double quotes(") Power BI is not able to recognize the data and is not able to Parse the JSON format to regular columns. I can't hardcode the double quotes(") based on character positions as the data is an array and the number of objects in the array is dynamic.

Is there a solution for my issue ?

Thanks & Regards,

Pratik

question from:https://stackoverflow.com/questions/65918980/is-it-possible-to-insert-text-in-a-column-conditionally-in-power-bi

Answer 1

If you are able to edit as a string I would prefer trying replacing like replace _id: with _id:" and replace ,Action: with ",Action ......