Forum Discussion

PowerQueryFTW's avatar
PowerQueryFTW
Frequent Visitor
7 years ago
Solved

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

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?  

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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

     

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    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's avatar
      PowerQueryFTW
      Frequent Visitor
      7 years ago

      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's avatar
        parry2k
        Super User
        7 years ago

        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

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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