Forum Discussion

gogzy's avatar
gogzy
New Member
7 years ago
Solved

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.

  • 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

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion

    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 )