Complex parent-child hierarchy question with transformation needed

Question

I have a slightly more complex task for the cracks among us. I am trying to learn steadily. I have attached an excerpt from a table that a customer supplied to me.

It is a list of OP codes that he wants to have transformed for a database.

_Key_CHOP: is the unique ID per row, and is composed of the year and the CHOP_Code (I have only inserted the relevant columns).
CHOP_Code: Code for an operation/procedure, which, however, can repeat itself in the years, therefore not unique.
nbchar: denotes the number of characters per CHOP code.

Now I am faced with the challenge of mapping the table into a hierarchy structure:

As an example, I have manually displayed the first 10 rows of what the result should look like:

_Key_CHOPCHOP_CodeLevel 1Level 2Level 3Level 4...... 2022_000      2022_0000000     2022_000000000000    2022_000100010000000001   2022_000200020000000002   2022_000300030000000003   2022_000900090000000009   2022_001001000001    2022_001000100000010010   2022_001100110000010011

The level structure must be dynamic, so for example in line 13 there is a fifth level.

Unfortunately, I don't have a unique parent-child structure, but the nbchar can be used for this.

Who has a speditive idea?

CHOP-Hierarchy_Example.yxmd

Christina_H · Accepted Answer

This should be dynamic to however many levels of code you have, I added an extra code with 5 levels and it still worked.