cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
George1973
Resolver II
Resolver II

Extracting Group indexes from a single column

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

George1973_0-1658861504889.png

 

NameCodeParent
ProdGroup_0002 
ProdGroup_1022002
ProdGroup_204258022
ProdGroup_30903204258
ProdGroup_30413704258

 

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:

 

George1973_1-1658861544762.png

 

 

Lvl0Lvl1Lvl2Lvl3
0020220425809032
0020220425804137

 

Thanks in advance for your suggestions,

Respecfully,

2 REPLIES 2
v-xiaotang
Community Support
Community Support

Hi @George1973 

Thanks for reaching out to us.

You can try this

vxiaotang_0-1659076001500.png

then click To Table in the uper left corner, then 

vxiaotang_1-1659076139296.png

delete 2 columns

vxiaotang_2-1659076186413.png

click Merge

vxiaotang_3-1659076220550.png

vxiaotang_4-1659076238586.png

result

vxiaotang_5-1659076252774.png

 

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
Resolver II
Resolver II

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:

 

George1973_2-1658927732029.png

 

 

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_CodePARENTPARENT levelTree Levels
0001700612
0110700612
0123700612
0168700612
0171700612
0189200612
0189600612
0190300612
0203400612
0206500612
0220200612
0220800612
0223100612
0226200612

 

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

Please help,

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors