Identifying Duplicates

[rmba22875]

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 make up a relationship which look like: 

AE001/AE002

AE001/AE004

AE001/AE005

AE001/AE006

AE001/AE008

AE001/AE009

AE001/AE010

AE001/AE012

AE002/AE001

These are always in alphabetical order and we currently use the following formula to identify duplicates.
=IF(ISERROR(INDEX($F$1:F1,MATCH(RIGHT(F2,5)&"/"&LEFT(F2,5),$F$1:F1,0))),"","Duplicate")

Is there something similar that can be done in PowerBI?

Thanks

[Anonymous · Accepted solution]

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]

Try the following : 

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

[rmba22875]

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")

[YukiK]

This should be done in Power Query if you're looking to remove duplicates. Go to query editor and you'll a plenty of data manipulation options there

If not, would you elaborate what you're trying to achieve? Are you trying to add a column that identifies duplicates?

[rmba22875]

Yes we need to apply a column that identifies the duplicates. This is so we can then manipulate the data as we need too as sometimes we need to look at it as a whole and other times without the duplicates.