How to create a hierarchy for difficult code format?

[smartin211]

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.

[Irwan]

hello @smartin211 

how about split by delimiter?

for example: 

become 

Thank you.

[smartin211]

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 · Accepted solution]

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.