Multi-level JSON column

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.