Forum Discussion

asifrasool's avatar
asifrasool
Helper I
3 years ago
Solved

Error While parsing JSON

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 wi...
  • amitchandak's avatar
    3 years ago

    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"