Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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_end_times
{"call_1": {"end_time": "01/21/2023, 07:44:11", "start_time": "01/21/2023, 07:39:16"}} |
{"call_1": {"end_time": "01/20/2023, 07:59:31", "start_time": "01/20/2023, 07:58:23"}, "call_2": {"end_time": "01/20/2023, 08:31:19", "start_time": "01/20/2023, 08:30:53"}, "call_3": {"end_time": "01/20/2023, 09:09:58", "start_time": "01/20/2023, 09:08:41"}} |
{}
so for some consultations, they have 1 call, some have 2 calls, some have 3 calls, some have 0 calls, ...etc. When I add a new custom column using
"Custom", each Json.Document([call_start_end_times])
then the new column becomes record.
when I expand this column, then the syntax shows = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"call_1"}, {"Custom.call_1"})
No other data or column showed out. it only shows when it is {}.
please let me know how to expand this column, since some have 0 call, some have 1 call, some have 2 calls..... I think I need to modify the syntax but in power query I can not load all the records out and it only showed {} records. Could u please guide me how to modify the syntax, so that the records that have 1 calls, 2 calls, 3 calls, etc can be expanded.
Solved! Go to Solution.
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