Forum Discussion
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-msftCommunity Support