Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
91 | |
87 | |
80 | |
69 | |
69 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |