Creating a Hierarchy of dependencies from two columns in Excel/Power BI

Question

I am working with Power BI for the first time. I have two columns: node1 and node2. The column relationship is that Node 1 depends on Node 2. Node 1 can have dependencies on multiple nodes. Also, Node 2 can be dependent on multiple nodes and so on. I want to create level wise dependency columns such as Level 1 (Root Dependency) -> Level 2 -> Level 3 -> Leaf Level for all nodes.

Node 1 | Node 2 | Outcome
A          B      D | C | B 
A          C      D | C 
B          C      D | C
C          D      D

I have tried creating data model with PowerBI to include function as follows:

PATH(Column 2, Column 1)

However it does not allow to have multiple values for one Node. Can anyone please suggest way to do this ?

Answer 1

As far as I know there is no recursion functionality in DAX, you may consider to leverage Power Query recursion capability to create additional column to work out the dependency path of each Node 2. You can refer to this repository for the coding which should be exactly what you are looking for : https://github.com/synru/powerquery.

It has a custom function for doing this. Good luck: https://github.com/synru/powerquery/blob/main/fn_Dependency_Chain.pq