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

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

9 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    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" )

    • rmba22875's avatar
      rmba22875
      Frequent Visitor
      3 years ago

      Thanks for this! Sorry I missed it previously.

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    it will be easy in M code

    NewStep=Table.Group(Table,"Column1",{},1,(x,y)=>let fx=(t)=>Text.Combine(List.Sort(Text.Split(t,"/")),"/") in Value.Compare(fx(x),fx(y)))

    in Dax, you can add a calculated column as this

    NewColumn=

    = VAR _c=test[Column1] RETURN MINX(FILTER(test,test[Column1]=_c||test[Column1]=CONCATENATEX({2,1},PATHITEM(SUBSTITUTE(_c,"/","|"),[Value]),"/")),'test'[Column1])=_c
    then you can filter out the false values to get unique value in column 1
    • rmba22875's avatar
      rmba22875
      Frequent Visitor
      3 years ago

      Thanks for this but do i need Power Query to do this? or can all of this go into power bi?

      Ive tried this so far with no joy:

      Duplicate =
      = VAR _c='Consolidated Summary'[Consolidated Home Relationship] RETURN MINX(FILTER('Consolidated Summary','Consolidated Summary'[Consolidated Home Relationship]=_c||'Consolidated Summary'[Consolidated Home Relationship]=CONCATENATEX({2,1},PATHITEM(SUBSTITUTE(_c,"/","|"),[Value]),"/")),'Consolidated Summary'[Consolidated Home Relationship])=_c
  • 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"
)