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 ...
@PowerBI Report
Duplicates
PowerBI Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi rmba22875 

    please try

    Duplicate =
    VAR T =
    VALUES ( 'Table'[Markers] )
    VAR HomeMarker =
    LEFT ( 'Table'[Markers], 5 )
    VAR Away =
    RIGHT ( 'Table'[Markers], 5 )
    RETURN
    IF ( Home > Away && Away & "/" & Home IN T, "Duplicate" )

FreemanZ's avatar
FreemanZ
Super User
3 years ago

hi rmba22875 

try like:

column2 = 
VAR _list = ALL(TableName[Column1])
RETURN
IF(
    RIGHT([Column1], 5)&"/"&LEFT([Column1], 5) IN _list,
    "Duplicate"
)

it workedl like:

 

rmba22875's avatar
rmba22875
Frequent Visitor
3 years ago

Ahhh Thank you however what i need is for the second one to only appear as the duplicate otherwise everything would have duplicate in that second column.
Currently my excel formula looks at everything before it and identifies the duplicate from that. Other wise we would get a similar problem with everything being a duplicate.

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    hi rmba22875 

    then you would need an index column, like:

    column2 = 
VAR _list =
CALCULATETABLE(
    VALUES(TableName[Column1]),
    ALL(TableName),
    TableName[Index]<EARLIER(TableName[Index])
)
RETURN
IF(
    RIGHT([Column1], 5)&"/"&LEFT([Column1], 5) IN _list,
    "Duplicate"
)
    • rmba22875's avatar
      rmba22875
      Frequent Visitor
      3 years ago

      My Table has been made from a different query so am currently trying to add an index manually! hopefully once done this will work!

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        hi rmba22875 

         

        tamej1's solution does not require an index column, please have a try. i tried and succeeded.