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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## 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 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
1 ACCEPTED SOLUTION
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

7 REPLIES 7
Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

Duplicate.pbix

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Helper I

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!

Super User

Duplicate.pbix

Measure tweaked and a bit more performant

 Expertise = List.Accumulate(        {Days as from Today},        {Skills and Knowledge},        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday))

Super User

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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!

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors