Forum Discussion
Count duplicate values across multiple columns
Hello,
I am new to Power BI and am interested in understanding how to identify duplicates across multiple columns.
In Excel, I used the created a Duplicates column and add the formula below. I would look in the four columns E,I,J and K for rows with an exact match, and it would list in the Duplicates column the number of duplicates it encountered for that row (e.g. 2, 3, 9, etc.)
=SUMPRODUCT(--(E2&I2&J2&K2=$E$2:$E$100&$I$2:$I$100&$J$2:$J$100&$K$2:$K$100)
In Microsoft Access, I was able to simply use the Find Duplicates Query Wizard to achive the same result.
How do I do this in Power BI?
Thanks!
Maybe this is just language, but if you want to keep the dupilcates and count them, then surely you will end up with this.
First Name Last Name Address Number of Duplicates
Row XYZ John Smith 123 Sandpiper Lane 3
Row XYZ John Smith 123 Sandpiper Lane 3
Row XYZ John Smith 123 Sandpiper Lane 3
If you want to remove the duplciates and keep 1 copy of every row, then you will end up with this.
First Name Last Name Address Number of Duplicates
Row XYZ John Smith 123 Sandpiper Lane 3
You can also do this in Power Query. Just select Group By, and then select Count. The button is on the home tab to the right hand side of the menu
Yes, Advanced Grouping works. Thank you
6 Replies
In power query (get data), you can highlight multiple columns and then either remove duplicates or keep duplicates.
Thank you,
So I want to keep the duplicates, but I also want a "count" of how many duplicates I have. And those duplicates would be across multiple columns.
For example:
First Name Last Name Address
Row XYZ John
Thank you,
So I want to keep the duplicates, but I also want a "count" of how many duplicates I have. And those duplicates would be across multiple columns.
For example:
First Name Last Name Address Number of Duplicates
Row XYZ John Smith 123 Sandpiper Lane 3
The number of duplicates column would show me that I have 3 duplicates of rows with this identical data.
I can do this in Excel and Access. How can I accomplish the same in Power BI?