Forum Discussion

bpflueger's avatar
bpflueger
Frequent Visitor
7 years ago

Manipulation of complex JSONs

Hello,

 

I'm trying to ingest a relatively complex JSON file into PowerBI - complex in the sense that there are multiple nested lists.  Here's an example snippet of the JSON (the full version has about 5,000 top level nodes):

{
   "items":[
      "an_item",
      {
         "VERSION":"1.0",
         "Execution_time":"0.454",
         "Info":"",
         "Rules":{
            "enabled":"t",
            "lic_end":"20181226",
            "lic_start":"20171204",
            "licensed":"t",
            "Rules":[
               {
                  "rule 1":"name"
                  "some stuff here":"other stuff here"
                  "other params":"etc"
                  "other params":"etc"
                  "other params":"etc"
               },
               {
                  "rule 2":"name"
		  "some stuff here":"other stuff here"
		  "other params":"etc"
		  "other params":"etc"
                  "other params":"etc"
               }
            ]
         }
      }
   ]
}

My issue, and it's probably mostly ignorance, is that I can't get the imported data formatted into a usable table.  When I try to split the original JSON into tables and columns as I've done in the past, I end up with a result like this (pardon the masked data):

 

Again, probably user error, but I cannot figure out how to get it into a workable format.  I basically need every other row moved into a new column so the "Record" values share a row with the node IDs.  I've never run into this issue with other JSON files, and I can't tell if this is a formatting issue with the JSON or something I'm doing wrong.  I've tried looking at a few other JSON guides and didn't see this particular situation mentioned unfortunately.

 

Thank you in advance, and let me know if I can provide any other details on what I've done!

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi bpflueger,

     

    I think that shoule be the issue of your json file. As I have a check of your code here. I got the error messages as below.

     

     

    Regards,

    Frank