Flattening an 'n' level JSON file

[Anonymous]

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

[mahoneypat]

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