Recursive Nested Child List/Record Objects, Add Column Value to Table

[qaschris]

Hi, i'm new to Power BI.  I have a development background, but learning M and the intricacies of Power BI has been a challenge.

I am working with some data-oriented reports, in this case pulling a JSON payload from a RESTful API, and working with that data.  I am receiving an object which has nested Lists of Record objects, each of which may have more nested Lists of Record objects.

The top level object appears as thus:

The property in which I'm interested is 'children'.  I am not interested in the 'links' property.

Drilling down, the objects are similar:

Expanded Record:

And further in:

And so on, for an unknown total object depth.

I'm trying to figure out how to navigate these structures recursively to make a cohesive but simple list of the 'id' property for every object in the structure.  I've searched extensively and the recursion methods I've found don't match up to the data structures that I'm using here.  The closest I found was Mike Honey's example of recursing tables, but as these aren't table objects to begin with, it doesn't quite fit the bill.  I would appreciate a little guidance from some more seasoned folks on a tidy way of doing this.

Thanks in advance for any advice!

[qaschris · Accepted solution]

Alright, so I applied the script that I linked in my last response, and got a completely flattened (if ugly) table that looks like this.

Once I had my flattened data, I filtered out the unnecessary columns, only keeping the first Value column and any column with "Name" in it as these are the dynamic columns that describe the data type.  I then unpivoted my columns to achieve the following.

I then filtered on the Value.1 column for all values that match only "id" and got my short list of object ids.

[Syndicate_Admin]

 Power Query supports recursive function calls - that would let you explore all the tendrils/ends of your hierarchy tree and collect the IDs from each.

[Syndicate_Admin]

JSON is usually giving you ragged hierarchies.  Power Query needs to produce a flat table.  Round hole, meet square peg. Square peg, meet round hole.

You will have to make decisions.

- what data do you need from the JSON

- what is the flat format that you want to feed to the Power BI part (the data model and UI)

There is no universal or simple answer.  You have to make decisions, and they can sometimes be painful, and destroy information along the way.

[qaschris]

Thanks for the reply.  In short, all I need is the "id" property from each of the nested objects so I don't care about losing the remainder of the data.  It's finding the way to dynamically and recursively navigate the heirarchies that's giving me trouble.  Since it's the structure of an API call over which I have no control, I'll have to process said structure within Power BI.  I may have found a rather complex script that might do the job but I won't know until I try to implement it within my project.