Forum Discussion

rmba22875's avatar
rmba22875
Frequent Visitor
3 years ago
Solved

Identifying Duplicates

We are currently trying to replicate some reporting we already do in excel and use Power BI instead and have hit a snag. Our data contains 2 identifying markers which we call Home and Away and...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  rmba22875 ,

     

    Here are the steps you can follow：

    1. In Power Query -- Add Column – Index Column – From 1.

    2. Create calculated column.

     

    Column 2 =
var _right=
VALUE(RIGHT([Column],3))
var _left=
VALUE(MID([Column],3,3))
return
IF(
    _left>_right,    
      RIGHT([Column],5)&"/"&LEFT([Column],5),
      LEFT([Column],5)&"/"&RIGHT([Column],5))

     

    Column 3 = 
var _select=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Index]<>EARLIER('Table'[Index])),"1",[Column 2])
return
IF(
    'Table'[Column 2] in _select ,"Duplicate","")

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

AmiraBedh's avatar
AmiraBedh
Super User
3 years ago

Try the following : 

= IF( CALCULATE( COUNTROWS(Table1), FILTER( Table1, Table1[Home] = EARLIER( Table1[Home] ) && Table1[Away] = EARLIER( Table1[Away] ) ) ) > 1, "Duplicate", "" )

rmba22875's avatar
rmba22875
Frequent Visitor
3 years ago

Given it a go and not working. I am using a column with the home and away values concatenated such as AE001/AE002 so the duplicate would be AE002/AE001.

 

Duplicate = if(CALCULATE(COUNTROWS('Consolidated Summary'),FILTER('Consolidated Summary','Consolidated Summary'[Consolidated Home Relationship]=EARLIER(right('Consolidated Summary'[Consolidated Home Relationship],5&"/"&LEFT('Consolidated Summary'[Consolidated Home Relationship],5),>1,"Duplicate")