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.
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!
Solved! Go to Solution.
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
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?
Hi @waltw,
Have you tried the solution provided by @MattAllington above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?
If you still have any question on this issue, feel free to post here.
Regards
Yes, Advanced Grouping works. Thank you
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |