Count duplicate values across multiple columns

[waltw]

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!

[MattAllington · Accepted 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

[MattAllington]

In power query (get data), you can highlight multiple columns and then either remove duplicates or keep duplicates. 

[waltw]

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

[waltw]

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?

[waltw]

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?

[v-ljerr-msft]

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

[waltw · Accepted solution]

Yes, Advanced Grouping works. Thank you

[MattAllington · Accepted 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