Forum Discussion
Identifying Duplicates accross Multiple Columns
Hello,
I am new to Power BI, and I am trying to find rows with duplicates across multiple columns.
How would I accomplish this in Power BI?
In Excel, I would create a new column called "Duplicates" and use the formula below. In the Duplicates table, it would list (eg 3, 4, etc.) For the number of times it found an exact duplicate for a row where columns E, I, J, and K contained the same values.
=SUMPRODUCT(--(E2&I2&J2&K2=$E$2:$E$10000&$I$2:$I$10000&$J$2:$J$10000&$K$2:$K$10000)
In Microsoft Access, I could use the Find Duplicates Query Wizard to accomplish the same thing.
How do I accomplish this in Power BI?
Thanks!
SELECT Count(*) AS NumberofDuplicates, Item, Size, Created, Modified
FROM YourTable
GROUP BY Item, Size, Created, Modified
HAVING count(*)>1
Was really looking for a count of duplicate items across multiple rows.
Went into Edit Queries
Selected Transform/Group By.
Clicked the Advanced radio button.
Grouped by my four columns: Item, Size, Created and Modified.
A new column of Count was already set.
Clicked OK
The Count column listed the number of duplicates encountered for each row.
3 Replies
Hi waltw,
In your resource table, you can create a calcualted column using the following formula.result=IF(CALCULATE(COUNTROWS(Table),ALLEXCEPT(Table,Table[Item],Table[Size],Table[Created],Table[Modified]))>1,TRUE(),FALSE())
If the row returns true, which instructs it with duplicates across multiple columns. So if you want to find out all the rows, you can create a new table by clicking "New Table" under Modeling on Home page, type the fomular below.
NewTable=FILTER(Table,Table[result]=TRUE())
Best Regards,
AngeliaWas really looking for a count of duplicate items across multiple rows.
Went into Edit Queries
Selected Transform/Group By.
Clicked the Advanced radio button.
Grouped by my four columns: Item, Size, Created and Modified.
A new column of Count was already set.
Clicked OK
The Count column listed the number of duplicates encountered for each row.Perfect solution for me also.
The capability to check row duplicates by checking the values of multiple columns to ensure we are going to remove absolute duplicates within the transformation stage and not just reused primary columns, like an email address along with First Name and Last Name, taking the column value of True where row duplicates exist and false where they do not, we can filter on True and filter these out within the transformation stage or filter and remove without absolute removal of duplication that may not actually be duplication.
Cheers Angela! ( v-huizhn-msft )
