Forum Discussion

waltw's avatar
waltw
Regular Visitor
9 years ago
Solved

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 "Dupl...
  • waltw's avatar
    waltw
    9 years ago

    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.

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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,
Angelia

waltw's avatar
waltw
Regular Visitor
9 years ago

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.