Filter matrix including one duplicates, but excluding other dups

[rogerdea]

Hello

I am trying to filter a list for duplicate values in one column, but only when another column does not have a duplicate value.

So in my example data i would want to pull out "Ref"  112244 because there is a duplicate and the "IncidentID" is different on both "Ref".   

I would not want to include "Ref" 130130" because this Ref has the same "IncidentID".  Hopefully this makes sense?

How would you go about achieving this filtered list?

Thanks

[v-chuncz-msft · Accepted solution]

@rogerdea 

You may add a calculated column.

Column =
ISEMPTY (
    FILTER (
        'Table',
        'Table'[Ref] = EARLIER ( 'Table'[Ref] )
            && 'Table'[IncidentID] <> EARLIER ( 'Table'[IncidentID] )
    )
)

[RobbeVL]

Hi,

Just select both those columns ref & ID, and remove duplicates in PowerQuery.

You should get your desired result.

Robbe

[rogerdea]

Thanks i have used power query to de-duplicate the IncidentID field, but for the Ref field, it's the duplicates only i want to display (excluding unique records).  Is this also possible in power query?