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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors