Parsing broken JSON

[Anonymous]

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.

[dax]

Hi FAM_USER,

It seems that you want to use M code to modify your JSON datasource, right? If possible, could you please inform me your JSON file and your expected output, then I will test and reproduce this in my environment. 

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I wasn't permited to post JSON. There is a decision taken that we shall create an SSIS import to SQL and make it our source so the data will be already in Power BI friendly format (tables only). Thank you for your commitment, unfortunately we won't be able to solve this problem here together.

[Anonymous · Accepted solution]

Hi,

I wasn't permited to post JSON. There is a decision taken that we shall create an SSIS import to SQL and make it our source so the data will be already in Power BI friendly format (tables only). Thank you for your commitment, unfortunately we won't be able to solve this problem here together.