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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
mcash
Helper I
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 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
1 ACCEPTED SOLUTION
parry2k
Super User
Super User

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

 

parry2k_0-1692729665502.png

 



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.

View solution in original post

7 REPLIES 7
parry2k
Super User
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.

parry2k
Super User
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.

ThxAlot
Super User
Super User

Duplicate.pbix

 

ThxAlot_0-1692734189903.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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!

Duplicate.pbix

 

Measure tweaked and a bit more performant

ThxAlot_0-1692766748885.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



parry2k
Super User
Super User

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

 

parry2k_0-1692729665502.png

 



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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.