Finding Duplicate "Recipes"

[mcash]

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 Number	Product Name	Component Number	Component Name	Quantity
575614	Product A	5969319	Component 1	25
575614	Product A	7507039	Component 2	4
575614	Product A	5217657	Component 3	42
575614	Product A	8553609	Component 4	10
575614	Product A	9648621	Component 5	19
611611	Product B	7507039	Component 2	60
611611	Product B	4214211	Component 8	18
611611	Product B	5217657	Component 3	20
611611	Product B	7880590	Component 13	2
470610	Product C	5969319	Component 1	25
470610	Product C	7507039	Component 2	4
470610	Product C	5217657	Component 3	42
470610	Product C	8553609	Component 4	10
470610	Product C	9648621	Component 5	19
448305	Product D	7507039	Component 2	45
448305	Product D	4214211	Component 8	25
448305	Product D	5217657	Component 3	15
448305	Product D	7880590	Component 13	15

[parry2k]

@mcash solution is attached, tweak it as you see fit. I would like to know how it performs with your dataset. How many rows do you have? Can you give a rough idea? And how many unique products do you have?

[parry2k]

@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!

[ThxAlot]

Duplicate.pbix

[mcash]

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!

[ThxAlot]

Duplicate.pbix

Measure tweaked and a bit more performant

[parry2k · Accepted solution]

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

[mcash]

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!