Hierarchy with same child name for different parents

[Mario1000]

Hi,

I'm working with a parent-child hierarchy but in which some child values have different parents. For instance:

Child	Parent
USA	Country
France	Country
Revenues	USA
Revenues	France

The hierarchy function PATH() does not support a hierarchy like that with same child for different parents. Is there any way to work with this kind of hierarchy?

Thanks!

[kentyler]

country	USA	Revenues
Country	France	Revenues

recast your hierarchy, the parent "country" has 2 children, "USA" and "France", each of them has one child "Revenues"

[Mario1000]

Yes, that's the hierarchy of the example. But now imagine that you have a table of thousands of rows instead of just 4. Then to build up the hierarchy you would use the PATH( <ID_ColumnName>, <Parent_ColumnName>) function, but this function 

returns an error if parent_columnName value is different for those duplicates  (which is the case).

Then my question was, how to build the hierarchy anyway? I'm afraid it's not possible...

[Zubair_Muhammad · Accepted solution]

@Mario1000 

Try this custom Column from Query Editor

=let //Define your columns below
 c=[Child],p=[Parent],mytable=#"Changed Type",pc="Parent",cc="Child" 
  in
let mylist={c} & List.Generate(()=>[x=0,y=p,w=1],each [w] > 0,each [z=[y], 
x=Table.Column(Table.SelectRows(mytable,each Record.Field(_,cc)=z),pc),y=x{0},w=List.Count(x)
],
each [y])
        in
Text.Combine(List.Reverse(List.RemoveItems(
List.Transform(mylist,each Text.From(_)),{null,""})),"|")