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

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