Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to Solution.
PATH() is correct. Replace all periods with pipes. Then use PATH.
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.
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?
I will post the follow-up question as a new question.
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.
PATH() is correct. Replace all periods with pipes. Then use PATH.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |