Imke Feldmann shows how to flatten JSON data imported into Power BI:
If you work with JSON documents that are new to you, it can be very helpful to fully expand JSON to see at a glance what’s in there. The following function has you covered for this task. It returns a table with all values in column “Value” and additional columns describing where that value came from in a hierarchical form, no matter how deep the nesting goes:
Click through for a script which shows that the process is a lot more complicated than I had expected.