Unroll data from self-referencing table

Hi,

I am a bit stuck on how to to handle some data in Power BI. I am quite new to it so I don’t even know if what I want to do is possible or not… my googling didn’t come up with anything successful, but maybe I am not using the right terms.

So I have two tables, “rule” and “thing”. The table “rule” references lines in the table “thing” as well as in itself :

What I want to do is being able to easily see which things are used in which rules. To do that, I created a new column in the table “rule” which goes like :

= Table.AddColumn(…, "LinkedThings", each if [#"action"]="do thing" then [target] else "null")

So I now have a “rule” table that looks like that :

And I added a relationship between rule.LinkedThings and thing.name so that I could see my data like that :

The problem here is that it doesn’t work for nested rules, for example rule2 is calling rule1 which calls thing1, so I should see that thing1 is linked to rule2. My end-goal visualization would be something like that :

Of course there is no limitation to the depth of rules references, I thought about creating a new column in the same idea that I did “rule.LinkedThings” and then having a second “rule” table and cross-reference between these but it’s quite ugly, and I would need X table for X depth…

I also tried to look for a function that I could used in a new column that would recursively search in its own table but to no avail.

I would be really happy for any help provided, I might also use the totally wrong tool for what I want to do, as I said I’m really new to Power BI so don’t hesitate to tell me if that’s the case 😊

Thank you and have a nice day !