Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

 

Thanks in advance!

Need Help

5 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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!