Multi-level JSON column

[Stanil]

Hi,

I have output from some API. There is a column "Entities" I want to extract, originally it is in JSON format. I want to have duplicated the rows for each Key-value pair extracted from Entities. 

I "unrolled" one level by using Table.ExpandListColumn:

but in the output I get different types of data in it: JSON records, JSON lists, or empty values. (Thanks, Microsoft, for not keeping up any standards in the LogAnalytics API).

Now, if I try to parse it again by Table.TransformColumns, I always stumble upon some errors.

I have some ideas, not sure which one are useful here: 

 a)  to convert the "record" type back to text and stop modeling the data

 b) to convert the "record" type into a list with one element (so add '[' and ']' at the beginning and the end of the record) and then unroll the list again

 c) to remove all brackets, and split the data by ',' to Key-Value Pair

 d) to convert the lists selectively into 2nd level records, without touching the 1st level records

I watched a few YT videos about converting JSON, but all of them (so far) assumed the JSON structure is flat and uniform for all the records. It's not the case here. Please point me in the right direction.

[v-kathullac · Accepted solution]

Hi @Stanil,

Thank you for reaching out to the Microsoft Fabric Community Forum.

I have taken a sample JSON file, loaded it into Power BI, and applied a few steps to split the columns.

Please find the screenshot and Power Query steps attached for your reference

Power Query:

let

    Source = Table.FromRows({

        {1, "[{""Key1"": ""Value1""}, {""Key2"": ""Value2""}]"},

        {2, "{""Key3"": ""Value3""}"},

        {3, null},

        {4, "[{""Key4"": ""Value4"", ""Key5"": ""Value5""}]"},

        {5, "{""Key6"": ""Value6"", ""Key7"": ""Value7""}"}

    }, {"ID", "Entities"}),

    Parsed = Table.AddColumn(Source, "ParsedEntities", each try Json.Document([Entities]) otherwise null),

    NormalizedList = Table.AddColumn(Parsed, "EntityList", each

        if [ParsedEntities] = null then {}

        else if Value.Is([ParsedEntities], type list) then [ParsedEntities]

        else if Value.Is([ParsedEntities], type record) then {[ParsedEntities]}

        else {}, type list

    ),

    ExpandedList = Table.ExpandListColumn(NormalizedList, "EntityList"),

    AddKV = Table.AddColumn(ExpandedList, "KV", each

        if Value.Is([EntityList], type record) then Record.ToTable([EntityList]) else null

    ),

    ExpandedKV = Table.ExpandTableColumn(AddKV, "KV", {"Name", "Value"}),

    Final = Table.SelectColumns(ExpandedKV, {"ID", "Name", "Value"})

in

    Final

Output:

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[lbendlin]

Read about Value.Is - then probe the type of each value and handle its content accordingly.

Or - enumerate through all "Entities"  in your original JSON and parse these with try ... otherwise ...