Error While parsing JSON

[asifrasool]

Hello Everyone. In my dataset I have one column which contains JSON data,
Data Sample: {"11_digit_identifier": "ZMOF1232@#$D", "anrede": "frau", "behandelnder_arzt": None}

When I try to parse it with the Transform>Parse>JSON, it gives an unknown error. (image is attached)

I even tried putting [ ] around the data in each row of that column but still it doesn't work, it gives following errors which is show in the image. any solution or lead would be highly appreciated.

This is the situation

In result, it generates this following error.

regards,

Asif Rasool

[asifrasool]

Hello @amitchandak thank you so much. The problem is that in my main datasource qoutation used is ' ' not " ", after doing this replace and putting " " around none and true false values, I am able to solve the issue, but there is one slight problem, which I will deal with that by some work around,

in 'diagnostic type': 'Heart's Disease'  when I replace ' ' to " " it becomes "diagnostic type": "Heart"s Disease" then this is the problem with the few of the entries.

But I understood what you said and it worked. Thank you so much. You are just Awesome.

I will also check your youtube channel to get more deeper understanding of M langauge and DAX.

[amitchandak · Accepted solution]

@asifrasool , Based on My testing None need to be in diuble quote.

Copy and paste this code in a blank query and check the steps. I added a replace step to add double quotes

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wqo5RMjSMT8lMzyyJz0xJzSvJTMtMLYpRslKIUYry9XczNDI2clBWcYlR0gGKJOYVpaakQmTTihJLIaJJqRmJeSmpOUBcFJ9YVFUCUuCXn5daqxQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Cleaned Text" = Table.TransformColumns(Source,{{"Column1", Text.Clean, type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Cleaned Text","None","""None""",Replacer.ReplaceText,{"Column1"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}}),
#"Parsed JSON" = Table.TransformColumns(#"Changed Type",{},Json.Document),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"11_digit_identifier", "anrede", "behandelnder_arzt"}, {"Column1.11_digit_identifier", "Column1.anrede", "Column1.behandelnder_arzt"})
in
#"Expanded Column1"