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
mmh9119
Frequent Visitor

Collapse duplicates and only keep those values for which the values align

Hello all,

I'm trying to achieve something fairly straightforward, but might be a bit tricky to explain. I've been using multiple table copies and vlookup in excel to do this, but I'm hoping there is a faster way to go about it using power bi.

I will explain with a simple example:

I have a group of duplicate numbers in column 1. In column 2, I have names of fruits (three only for this simple example). Columns 3 to 5 correspond to whether a row has a specific fruit (called Has_Apple, Has_Mango and Has_Cherry respectively), and contains a value of 1 corresponding to the column that refers to the row having that fruit. So if a row in column 2 contains the string Apple, Has_Apple for that row will be 1, while the other two columns will be 0.

What is needed is to determine if a group/collection of duplicate numbers has 1s occurring for all three columns, and if so, collapse the group of duplicates. After the process, the value of the fruit in column 2 isn't important, but you can imagine it containing the names of all three fruits. The columns 3 to 5 will predictably contain all 1s only for that collapsed row representing the entire collection. If, however, 1s are NOT occurring for ALL THREE columns, then the entire group of duplicates should be discarded.

As I mentioned, I was using multiple tables and vlookups in excel for this before, but I'm wondering if this can be automated in the manner described above using power bi. I've attached a sample pbix with the original dataset, and a results set which shows what the result of such a procedure would look like.

 

pbix example file 


Thanks for any help!

1 REPLY 1
lbendlin
Super User
Super User

That doesn't sound like a scalable approach, assuming you will want to test for other combinations too.

 

I would ditch all the flag columns and implement this purely based on the fact table and an appropriately structured reference table.

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.