cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Objekt
Frequent Visitor

Splitting Product name from component list

Hi All,

 

Below I have a list of ingredients with the final product listed above them. What I'm trying to do is remove each row with the product name and create a new column which lists the relevant product for each ingredient. How do I do this? I've looked at unpivoting columns but that seems to split out all ingredients and products into new columns. 

Product names can vary a lot but can be identified when the 'Level' column is equal to 0, any other number under 'Level' is an ingredient or an ingredient of an ingredient. Also there are around 600 products with relevant ingredients in the list.

Objekt_0-1630572930132.png

 

Thanks in advance!

1 ACCEPTED SOLUTION

@Objekt

i have updated the DAX and this time I add more product in the sample data. pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User
Super User

@Objekt 

maybe you can use DAX to create a new table.

 

Table 2 = 
VAR tbl=ADDCOLUMNS('Table',"product",maxx(FILTER('Table','Table'[key]<=EARLIER('Table'[key])&&'Table'[level]=0),'Table'[Object Description]))
return FILTER(tbl,'Table'[Item No]<>0)

 

 pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Objekt
Frequent Visitor

@ryan_mayu Thank you for this, your attachement seems to produce 'product 1' for all rows when rows 11-21 (key) should be product 2, is there a way to do this? Also some of the real product names don't have anything in common in their name so referencing to "Product" wont work, is there a way to reference Level or item number to 0? 

Thanks for your help so far!

@Objekt 

sry, there is sth wrong in the previous DAX. I have fixed it.

pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Objekt
Frequent Visitor

Hi @ryan_mayu,

Thanks, this works but only for the first 5 products or so, then it just repeats out the 5th product for the rest of the 'product' column. However, I changed the product names from their actual ones to a more simpler 'product 1,2,3' etc and this works. Do you know why it doesn't work for the actual product names? I've a feeling its the maxx function.

Thanks

@Objekt

i have updated the DAX and this time I add more product in the sample data. pls see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!