Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
George1973
Helper V
Helper V

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
Helper V
Helper V

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.