How parsing multi level nested json file in Power Query

Question

Hi,
I'm struggling right now to find an easy and efficient way to parse multi level nested json file. 🙄
I can import json file in Power query Editor --> convert it to table --> extend records --> extract lists.

But it's very heavy task to do that many times for a complex big json file 😕

And also there is a difficult case when there are records and lists in the same column.

I ask if there is a efficient way to parse all multi level nested json file that will work for different kind of json file?

@Miguel Escobar @Chris Webb

Thanks in advance,
Mourad

Answer 1

I forgot to mention this, but there's a preview feature called "json table inference" which could help you quite a bit:
]1

Answer 2

I do agree. Flattening a json file could be a tedious task, but at the same not all json files are created exactly the same and flattening without knowing the structure of the file could yield some denormalization that you might be trying to avoid.

There are methods to recursively look through the json file and flatten everything, but I try to avoid doing that and have full control over what I do with the json file.

Answer 3

Hi

If you up to learn more about PowerQuery M language, here are some documented examples how to read complex json files with deep hierarchy.

https://github.com/vinej/PowerQuery

Thanks