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!
