Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
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.
@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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |