Forum Discussion
Flattening an 'n' level JSON file
Hi,
I'm trying to turn an (theoretically) infinitely nested JSON into a table, similar to the below.
I'm not sure where to start as the moment PowerQuery reads it as a JSON I'm doomed to recursively navigate through it
| account | project | type | name | path | _id | shared_id |
| AEC_Control | 21b37e20-d2... | transformation | Default | 814d8939-6e… | 814d8939-6e7d-4412-9db5-a2fcc393658a | f094ff2a-f770-480d-a18c-ede9e14e2b7e |
| AEC_Control | 21b37e20-d2... | transformation | (IfcBuilding) | 814d8939-6e… | 46a684ca-879b-4308-a02b-9c7fbeb0f2a0 | 00482a85-feed-4f36-a476-3c5b9d666a11 |
| AEC_Control | 21b37e20-d2... | transformation | RF - Roof Level | 814d8939-6e… | c8da1f59-3959-4ac2-b702-1ccf86bd2ddc | 1157c6d9-6c74-4f3a-aa04-b75ea5f2e1a4 |
| AEC_Control | 21b37e20-d2... | transformation | Basic Roof -190761:EPDM Membrane on Rigid Insul on Metal Deck:314949 | 814d8939-6e… | f9a39fb7-d1dd-48b3-a59d-7440b10b9ada | bf51f351-8d5a-4ad2-9633-b06a22742e9d |
1 Reply
Since you only provided an image and not the json file, I couldn't try this out directly, but you should be able to do the following
- in the query editor, connect to the file as text instead of json
- use Text.Select to keep just numbers, letters and ":"
- filter out the rows that aren't your 8 repeating value pairs (account, project, type, etc.)
- split the column at the ":"
- add a column that creates an "index" with repeats of 8 (1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,3,3 ...)
- pivot the data to get your desired result
Regards,
Pat
