Forum Discussion

mcash's avatar
mcash
Helper I
3 years ago
Solved

Finding Duplicate "Recipes"

I have a dataset of products and their "recipe" or component blend. I need a way to find Products that have the same Components AND respective quantities of each component in the Product blend. I have provided an example scenario below. In the example Product A was made and manufactured, but then later on the company wanted to sell Product A under a different name, Product C. Product C has the same exact recipe as Product A. I need a way for PowerBI to find and display which products are duplicate recipes of each other. In this example Product B and Product D have the same components, but not at the same quantities. I would like to ensure the code recognizes that these products are not duplicates based on their quantities.

 

All that said, how can I display a simple table that says Product A = Product C based on this example?

 

Thanks

 

Example dataset:

 

Product NumberProduct NameComponent NumberComponent NameQuantity
575614Product A5969319Component 125
575614Product A7507039Component 24
575614Product A5217657Component 342
575614Product A8553609Component 410
575614Product A9648621Component 519
611611Product B7507039Component 260
611611Product B4214211Component 818
611611Product B5217657Component 320
611611Product B7880590Component 132
470610Product C5969319Component 125
470610Product C7507039Component 24
470610Product C5217657Component 342
470610Product C8553609Component 410
470610Product C9648621Component 519
448305Product D7507039Component 245
448305Product D4214211Component 825
448305Product D5217657Component 315
448305Product D7880590Component 1315
Need Help

7 Replies

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    ThxAlot Great solution but I believe this will not work for the following two conditions:

     

    - if the component count is equal but the component code is different.

    - if the component count is equal, the component code is equal but the quantity is different.

     

    The requirement is an exact match, the same number of components, the same components, and the same quantity. 

     

    I think that is what I understood from the requirements. Best!

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    mcash is this what you are looking for? How big is your table?

     

     

    • mcash's avatar
      mcash
      Helper I
      3 years ago

      Yes that's exactly what I'm looking for assuming it will also show more than one product if it matches. My table is very large, but I cannot share it which is why I made an example with fictitious information. Can you show me how you accomplished that?

       

      Thanks!

    • mcash's avatar
      mcash
      Helper I
      3 years ago

      Hi ThxAlot 

      Thank you for the code. I tossed it into my dashboard, and

      1. it hits the resource limit so I will need to figure out a way to minimize that. Any recommendations?
      2. it pulls products with the same components but not at the same quantities. This is still actually very useful, but I would like to be able to find exact matches too.

      Thanks!