Forum Discussion
How to import a json column into PowerBI
Hello! when importing a postgresql table into powerBI, there is a column which is JSON. This column is about for each consultation the call start and end time. the examples is as below: call_start_e...
Hi Anonymous,
I'd like to suggest you convert the Json.Document function result to table first before you expand the columns.
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZC7DsIwDEV/pfIcCTsPSO6vNFVVQQekwgDZUP+9LgzAAI3k5crH50puW3pkOg7T1EsmNBrG66kv58u4xkwsO6vD1pmGD/AeoqDRzb0Mt/KbdAmyzzTP1JmaEn6fhgT3p+STjLBOS1b06bfb/qhySKrwK8kIX3637U/QCbHCr2SEl9eTugU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JsonData = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"JsonData", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.ToTable(Json.Document([JsonData]))), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Value"}, {"Name", "Value"}), #"Expanded Value" = Table.ExpandRecordColumn(#"Expanded Custom", "Value", {"end_time", "start_time"}, {"end_time", "start_time"}) in #"Expanded Value"Result:
Regards,
Xiaoxin Sheng
Hi Anonymous,
I'd like to suggest you convert the Json.Document function result to table first before you expand the columns.
Full query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZC7DsIwDEV/pfIcCTsPSO6vNFVVQQekwgDZUP+9LgzAAI3k5crH50puW3pkOg7T1EsmNBrG66kv58u4xkwsO6vD1pmGD/AeoqDRzb0Mt/KbdAmyzzTP1JmaEn6fhgT3p+STjLBOS1b06bfb/qhySKrwK8kIX3637U/QCbHCr2SEl9eTugU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JsonData = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"JsonData", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Record.ToTable(Json.Document([JsonData]))),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Value"}, {"Name", "Value"}),
#"Expanded Value" = Table.ExpandRecordColumn(#"Expanded Custom", "Value", {"end_time", "start_time"}, {"end_time", "start_time"})
in
#"Expanded Value"Result:
Regards,
Xiaoxin Sheng