Forum Discussion

smartin211's avatar
smartin211
Regular Visitor
1 year ago
Solved

How to create a hierarchy for difficult code format?

I am perplexed on the best way to create a hierarchy out od this Taxonomy code column, can anyone help me? Auto hierarchy does not work for me.
Need Help
  • v-aatheeque's avatar
    v-aatheeque
    1 year ago

    Hi smartin211 ,
    Thank for the clear explanation.

    You’re trying to build a custom hierarchy from a structured taxonomy code like BH-3500.3400-300 so that:

     

    • BH is the top-level parent
    • BH-3500 is the second-level child
    • And any entries like BH-3500.3400-300 should fall under this BH-3500 in a Power BI matrix visual.

    you can create custom column for the Parent as below : 

    Parent = 
LEFT([Taxonomy Code], FIND("-", [Taxonomy Code]) - 1)

     Then for child :

    Child = 
VAR FirstPart = LEFT([Taxonomy Code], FIND("-", [Taxonomy Code] & "-") - 1)
VAR AfterFirstDash = MID([Taxonomy Code], LEN(FirstPart) + 2, LEN([Taxonomy Code]))
VAR SplitDot = FIND(".", AfterFirstDash & ".", 1)
RETURN 
IF(
    SplitDot > 1,
    FirstPart & "-" & LEFT(AfterFirstDash, SplitDot - 1),
    [Taxonomy Code]
)

     the expected output :

    Hope this helps !!

     

    If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.





Irwan's avatar
Irwan
Super User
1 year ago

hello smartin211 

 

how about split by delimiter?

for example: 

become 

 

Thank you.

smartin211's avatar
smartin211
Regular Visitor
1 year ago

This won't create a hierarchy within a table matrix visual inside Power BI though. I did try this and it did not work as I was hoping. 

What I am trying to do is break this down in to 2 items; Take BH-3500.3400-300 for example. BH is the parent, BH-3500 is the child. Anything that is under BH-3500 shows within that BH-3500 matrix/pivot, etc.... so if there was a BH-3500.3500 or BH-3500.3500-200 it shows under BH-3500. Does that make better sense of what I am trying to accomplish?

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    1 year ago

    Hi smartin211 ,
    Thank for the clear explanation.

    You’re trying to build a custom hierarchy from a structured taxonomy code like BH-3500.3400-300 so that:

     

    • BH is the top-level parent
    • BH-3500 is the second-level child
    • And any entries like BH-3500.3400-300 should fall under this BH-3500 in a Power BI matrix visual.

    you can create custom column for the Parent as below : 

    Parent = 
LEFT([Taxonomy Code], FIND("-", [Taxonomy Code]) - 1)

     Then for child :

    Child = 
VAR FirstPart = LEFT([Taxonomy Code], FIND("-", [Taxonomy Code] & "-") - 1)
VAR AfterFirstDash = MID([Taxonomy Code], LEN(FirstPart) + 2, LEN([Taxonomy Code]))
VAR SplitDot = FIND(".", AfterFirstDash & ".", 1)
RETURN 
IF(
    SplitDot > 1,
    FirstPart & "-" & LEFT(AfterFirstDash, SplitDot - 1),
    [Taxonomy Code]
)

     the expected output :

    Hope this helps !!

     

    If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.