Forum Discussion
Working with JSON and multiple List
Many thanks. I actually watched the first video you are referencing just before posting this question 🙂 it does not contain the answer sadly.
I also watched https://www.youtube.com/watch?v=-QO57RHzxus which was quite helpful for the first few steps.Now, after posting this I just found this:
https://community.powerbi.com/t5/Desktop/How-to-expand-multiple-columns-to-new-rows-at-the-same-time/m-p/727833#M351265Which seems to solve my problem!
Hi SysLostInBI
I think your json file describes 16 records: 2 dimensions x 2 timestamps x 2 values => 2 x 2 x 2 = 8 and that 2 times.
So the expanded result in Power Query is:
If your json file looks as following you get the expected result.
{
"result": [
{
"metricId": "xx",
"data": [
{
"dimensions": "APPLICATION-aa", "typ":"SATISFIED", "timestamp":1585872000000, "values":241
},
{
"dimensions": "APPLICATION-aa", "typ":"SATISFIED", "timestamp":1586044800000, "values":1067
},
{
"dimensions": "APPLICATION-bb", "typ":"FRUSTRATED", "timestamp":1585872000000, "values":172
},
{
"dimensions": "APPLICATION-bb", "typ":"FRUSTRATED", "timestamp":1586044800000, "values":771
}
]
}
]
}
Regards FrankAT
Thanks, but no. The JSON is actually formed as I described it.
It originates from a commercial product and I have to deal with it 🙂