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

[tamerj1 · Accepted solution]

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]

Thanks for this! Sorry I missed it previously.

[FreemanZ]

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]

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]

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]

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]

hi @rmba22875 

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

[wdx223_Daniel]

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]

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