Forum Discussion
Error While parsing JSON
- 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"
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"