split an indented hierarchy

[DeanMcK]

Hi All,

Any help/Guidance appreciated in splitting up a hierarchy that has spaces and '|'.

I have attached an example.

Hierarchy
ABC1 Aust
|--5 1000000
|
|--DUMMY P
|
|--5 1000010
| |
| |--1000010
| |
| |--5 1000088
| | |
| | |--1000088
| | |
| | |--5 1000006
| | | |
| | | |--1000006
| | | |--1003910
| | | |--1003911
| | | |--A100050
| | | |--A100049
| | | |
| | | |--5 1000021
| | | | |
| | | | |--1000021
| | | | |
| | | | |--5 1000132

[TMS]

Maybe use Text to Columns delimited with space and hyphen/minus/dash. Output to column B. Replace all "|" with "".

[Matze68]

Hi Dean,
what are you aiming for?
Extracting all parent child relations or just some kind of formatting?

[DeanMcK]

Hi Matze,

I need to get it split to Parent/Child node at each level so I can reconcile the Parent/Child against other data.

Cheers

Dean

[alansidman]

See attached and if this is what you are looking for, then you can split the data in Power Query using the Pipe delimiter.

[Matze68]

First of all you have to determin for every element
- level
- does it have children
then the number of father elements on this level above...see attachment

Handling hierarchies looks like a simple task, but is is not.
I strongly recommend to use a database for this purpose.

HTH
Matze

Hierarchy.PNG