New Column to concatenate text for nested tree hierarchies

[JoeFields]

I have a table of data that is a list of nested tree nodes and I am looking for a way to concatenate a specific column with the "name" data to display the full path to each node excluding the root. Here is a simplified example:

parent_id	id	name	full path
	0001	Root
0001	0002	Level 1	Level 1
0002	0003	Area A	Level 1 -> Area A
0002	0004	Area B	Level 1 -> Area B

I have the columns above except the "full path" which I need to write a DAX expression to create. I need a new colum formula that can loop through each line and lookup the parent_id in the id column and return the name to prepend it to the original node name then continue until it hits a parent_id that is blank indicating it is at the root. I don't need the root name in the full path.

Thanks!

[wdx223_Daniel]

[amitchandak]

@JoeFields , Have you alreay tried path in power bi  and it did not helped you ?

https://radacad.com/parsing-organizational-hierarchy-or-chart-of-accounts-in-power-bi-with-parent-ch...

https://www.thebiccountant.com/2019/10/03/parent-child-hierarchies-with-multiple-parents-in-power-bi...

[JoeFields]

I have not tried Path but it might get me a little bit closer. My issue is that I need Path but with more of a key-value pair lookup where the id and the parent-id is used to find each parent-child relationship but then have it use the name column to create the path. Path appears to only work for the unique id. I might be able to get there if I can build the path then parse each id in the path to create the path using the name columns. Basically use an intermediate column with the path (based on ids) then iterate through each piece of the path to make the path (based on names). How can I loop through the path and do a lookup to replace the id with the name?

Also, I am trying to get the Path to work but getting this error. It appears to not like if parent_id column contains blanks but a blank means it is at the root. Documentation indicates Path should be able to handle blanks and I would assume this is how it knows it has hit the root.

Here is a snapshot of my actual data to show the structure I have. Each id is a GUID and the depth of the branches can vary but most likely no more than 10 levels deep at the most extreme.

[rfigtree]

orphan records.

you have dirty data. (omg, now i know why i couldnt get it to work)

maybe change orphan record parent key to blank (or delete) before letting Dax get hold of it.

[rfigtree]

yeah dax path needs a certain structure of data to work.

Try using power query, with self joins.

I exploded a BOM structure down to 7 levels using power query and it worked well.

Can get a little slow if you have a big data structure but does the job.

(just remebered utilizing Table.Buffer() in PQ. Speeds it up a bit, or maybe i was just imagining it, who knows?)

Guess you could MS ACCESS or other DB if Power Query not up to the job.