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 

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.