cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gogzy Frequent Visitor
Frequent Visitor

Substituting values for IDs in PATH

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Substituting values for IDs in PATH

@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 )
3 REPLIES 3
Super User
Super User

Re: Substituting values for IDs in PATH

@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 )
Super User
Super User

Re: Substituting values for IDs in PATH

@gogzy 

 

Please see the attached file as well

 

path.png

gogzy Frequent Visitor
Frequent Visitor

Re: Substituting values for IDs in PATH

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