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

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

     

5 Replies

  • YukiK's avatar
    YukiK
    Impactful Individual
    3 years ago

    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's avatar
      rmba22875
      Frequent Visitor
      3 years ago

      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.


  • 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")
  • Anonymous's avatar
    Anonymous
    Not applicable
    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