Hi,
I have JSON in a column that I have to parse. Unfortunately JSON I have isn't good quality one - there are quotations missing. I tried to parse it with R but I got to know it's not supported in MS Cloude Service, where the report is supposed to be published to, so I have to deal with it any other way. What my coworkers already did is to replace in M every occurance of certain keys and delimiters but it doesn't work all the time. I suspect it's inserting double quotes sometime.
Here is how the data look in general:
[{key:"DigitsOnlyValue",anotherKey:"StringValue",yetAnotherKey:datetime,oneMoreKey:null},{<anotherElementOfArray>}]
As you can see no keys have quotations. When it comes to values it's slightly better, as most of them is wrapped but datetime ones aren't. Few examples ot them (including space in the middle):
- 2019-10-08 12:43:52.57
- 2019-10-04 12:44:00.008
- 2019-09-16 12:23:07.172
- 2019-10-06 12:43:52.57
- 2019-11-05 11:44:44.3
Nulls seem okay for me and I haven't encounered any true/false values in my dataset.
What would be the best way to handle this? Doing it in Power Query would be the best!
@MarcelBeug, maybe you could help me? I've seen lots of your great responses.
