Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gogzy
New Member

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
Zubair_Muhammad
Community Champion
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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
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 )

Regards
Zubair

Please try my custom visuals

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

@gogzy 

 

Please see the attached file as well

 

path.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.