Create a hierarchy from a single field

[des_san]

Hi everyone, I need help creating a hierarchy from a single field, Project ID, that can be used in reports. Here is an example of what the data in that field looks like:

PROJECT

PROJECT.01

PROJECT.01.01

PROJABC

PROJABC.001

PROJABC.001.01

PROJABC.001.01.M01

PRJ1234

PRJ1234.ADM

PRJ1234.ADM.123

...

How should I go about this?

I saw a other posts that reference PATH() but that doesn't look like the right solution.

[lbendlin · Accepted solution]

PATH() is correct.  Replace all periods with pipes.  Then use PATH.

[des_san]

Thanks @lbendlin and @v-xinruzhu-msft .
I was reluctant to use PATH at first because it requires me to create a custom column for each level of the Project ID. Is there a more dynamic/flexible solution that can adjust automatically to the Project ID levels? 

Follow up question:
I created a hierarchy field with the Path Level fields. Is it possible to hide levels that are blank (or skip them)? In the example above, the first project has 3 levels and the second project has 4 levels.  As a result, the first project is showing a redundant blank 4th level. How would you approach this?

[des_san]

I will post the follow-up question as a new question. 

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @lbendlin  provided, it is excellent, and i want to offer some more information for user to refer to.

hello @des_san , based on your description, you can refer to the following solution.

Sample data 

You can create the following calculated columns

Path = SUBSTITUTE([Column1],".","|")

Level 1 = PATHITEM([Path],1)

Level 2 = PATHITEM([Path],2)

Level 3 = PATHITEM([Path],3)

Level 4 = PATHITEM([Path],4)

Output

If the solutions @lbendlin  and i provided can help you solve the problem , please consider to mark them as a solution.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

PATH() is correct.  Replace all periods with pipes.  Then use PATH.