How to import a json column into PowerBI

[Anonymous]

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. 

[Anonymous · Accepted 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