Nested JSON data with different columns for each file

[Anonymous]

Hello!

I am just posting to ask for some greatly needed help with nested JSON data in a powerBI report. The client I am working with has JSON data nested in a blob storage, where there are 13 types of JSON file, each type having a different set of column headers.

Issue 1 - Is there a way to dynamically set a power query that separates all of the different JSON file types and expands them into their own queries so that the different types are grouped together and so that power bi can work with the data?

Issue 2 - When I expand the time column and then expand a different column, the time column values suddenly all become 0. In for example Type1 we have the following headers:

Time, HeadPos, HeadRot, inside time we have each individual time point, HeadPos has to be expanded for x, y and z coord and same for Head Rot. If head pos and head rot are expanded, time values go from logical increments to just 0 across all the data. As each JSON file is linked to an individual object id, is there a way for a user to be able to just pull a singular JSON file from the blob storage?

If anyone can help at all it would absolutely save my day.

Thanks!

[Anonymous]

Hi @Anonymous,

AFAIK, you can create query table that references the main table records. Does any obviously Identifier stored in your records that can be used the recognize these records?

If that is the case, you can create blank query tables with if statements to check detail properties and filter the same structure records to grouped with different query tables.

BTW,  can you please share some dummy data with raw data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng