Forum Discussion
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.
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.
3 Replies
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?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.
