Extracting Group indexes from a single column

[George1973]

Hi Everyone,
I've got the following sample data:

Name	Code	Parent
ProdGroup_0	002
ProdGroup_1	022	002
ProdGroup_2	04258	022
ProdGroup_3	09032	04258
ProdGroup_3	04137	04258

As you can see, in the "Code" field are given not only the product codes, but also product group codes, which can be defined by "Parent" column (That they are really group indexes)

I would like to add calculated columns to that table, showing Groups and Subgroups in a right order, like given below:

Lvl0	Lvl1	Lvl2	Lvl3
002	022	04258	09032
002	022	04258	04137

Thanks in advance for your suggestions,

Respecfully,

[v-xiaotang]

Hi @George1973 

Thanks for reaching out to us.

You can try this

then click To Table in the uper left corner, then 

delete 2 columns

click Merge

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[George1973]

Additional Hint:
We have solved the issue in excel, with Vlookup function, but I can not create a measure in DAX for some reason. Here is a solution in excel:

Here is the formula:

=IF(C2="",1,(VLOOKUP(C2,B:E,4,FALSE)+1))
This formula generates 1,2,3...N ranges of the subgroup, helping me in defining the levels of the product categories. It's better solution what I wanted initialy.

Here is the sample data:

Prod_Code	PARENT	PARENT level	Tree Levels
00017	006	1	2
01107	006	1	2
01237	006	1	2
01687	006	1	2
01717	006	1	2
01892	006	1	2
01896	006	1	2
01903	006	1	2
02034	006	1	2
02065	006	1	2
02202	006	1	2
02208	006	1	2
02231	006	1	2
02262	006	1	2

 I can not apply Lookupvalue measure, I do not why :))))

Please help,