Substituting values for IDs in PATH

[gogzy]

I have a table which represents a hierarchical folder structure, along these lines:

+----+----------+------------+
| ID | parentID | folderName |
+----+----------+------------+
| 1  |          | Folder A   |
+----+----------+------------+
| 2  | 1        | Folder B   |
+----+----------+------------+
| 3  | 2        | Folder C   |
+----+----------+------------+
| 4  | 1        | Folder D   |
+----+----------+------------+

I would like to calculate the hierarchical path for each of the folders, using their names rather than IDs. I understand how to use the PATH function, but I'm not sure how to subsitute the ID for folderName.

Example of desired output:

+----+----------+------------+----------------------------+
| ID | parentID | folderName | folderPath                 |
+----+----------+------------+----------------------------+
| 1  |          | Folder A   | Folder A                   |
+----+----------+------------+----------------------------+
| 2  | 1        | Folder B   | Folder A|Folder B          |
+----+----------+------------+----------------------------+
| 3  | 2        | Folder C   | Folder A|Folder B|Folder C |
+----+----------+------------+----------------------------+
| 4  | 1        | Folder D   | Folder A|Folder D          |
+----+----------+------------+----------------------------+

Notes:
- Folder names are not unique, though the IDs obviously are
- The real data has a hierarchy which spans 20+ levels, and uses GUIDs rather than simple IDs

Sample data

I've put together a file with some mockup data, which can be downloaded here:
https://www.dropbox.com/s/qciflag2d4p671l/PathMockdata.pbix?dl=0

Thanks in advance for any help you can provide.

[Zubair_Muhammad · Accepted solution]

@gogzy 

Check this calculated column please

PATH_ =
VAR temp =
    ADDCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES ( 1, PATHLENGTH ( [folderPath] ) ),
            "MyID", PATHITEM ( [folderPath], [Value] )
        ),
        "Name", LOOKUPVALUE ( [folderName], [ID], [MyID] )
    )
RETURN
    CONCATENATEX ( temp, [Name], "|", [Value], ASC )

[gogzy]

It works perfectly. Thank you so much for your help.

[Zubair_Muhammad]

@gogzy 

Please see the attached file as well