Remove Duplicates when the key ID's fall within the same date ranges

[PowerQueryFTW]

I have a table that tracks advertising campaigns. 

The table has a key ID column, and then campaign start and end dates.  Is there a way to identify duplicate IDs that are within the same start and end dates using either DAX or Power Query?  

[v-juanli-msft · Accepted solution]

Hi @PowerQueryFTW

You could create calculated columns with DAX

min start date =
CALCULATE (
    MIN ( [start date] ),
    FILTER (
        ALL ( Sheet1 ),
        [keyid] = EARLIER ( [keyid] )
            && [start date] <= EARLIER ( Sheet1[end date] )
            && [end date] >= EARLIER ( [start date] )
    )
)

max end date =
CALCULATE (
    MAX ( [end date] ),
    FILTER (
        ALL ( Sheet1 ),
        [keyid] = EARLIER ( Sheet1[keyid] )
            && [start date] <= EARLIER ( Sheet1[end date] )
            && [end date] >= EARLIER ( [start date] )
    )
)

count of dicuplates =
CALCULATE (
    COUNT ( Sheet1[keyid] ),
    FILTER (
        ALLEXCEPT ( Sheet1, Sheet1[keyid] ),
        [min start date] = EARLIER ( Sheet1[min start date] )
            && [max end date] = EARLIER ( Sheet1[max end date] )
    )
)

if true = IF([count of dicuplates]>1,"TRUE","FLASE")

Or measures

min start date1 = CALCULATE(MIN([start date]),FILTER(ALL(Sheet1),[keyid]=MAX([keyid])&&[start date]<=MAX(Sheet1[end date])&&[end date]>=MAX([start date])))

max end date1 = CALCULATE(MAX([end date]),FILTER(ALL(Sheet1),[keyid]=MAX(Sheet1[keyid])&&[start date]<=MAX(Sheet1[end date])&&[end date]>=MAX([start date])))

count of dicuplates1 = CALCULATE(COUNT(Sheet1[keyid]),FILTER(ALLEXCEPT(Sheet1,Sheet1[keyid]),[min start date]=MAX(Sheet1[min start date])&&[max end date]=MAX(Sheet1[max end date])))

if true1 = IF(MAX([count of dicuplates])>1,"TRUE","FLASE")

Best Regards

Maggie

[parry2k]

@PowerQueryFTW you can use group by function in power query. use 3 field to group by (Key ID, Start Date and End Date)

- add count calculation

- add all rows 

expand all rows to get your table, and count calculation has value more than 1 , it means there are duplicates and also it will show # of duplicates. here is quick screen shot of group by on one of  sample table and you can use similar for your table

[PowerQueryFTW]

This would only show duplicates if there were exactly matching dates.  Row 2 and row 4 would not show as duplicates in your solution even though 6/15 is within 6/1 and 6/30

[parry2k]

@PowerQueryFTW you are correct, it will give you duplicate where all these 3 values matches, I misundersood your question. If you can share sample data it wil help to put together some solution for you.

Thanks,

P

[PowerQueryFTW]

I dont know how to share a file on here.  Were you able to see the photo of the data i attached to the original message?