cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bpflueger Frequent Visitor
Frequent Visitor

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):

json4.PNG

 

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 1
Community Support Team
Community Support Team

Re: Manipulation of complex JSONs

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.

 

json.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.