Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Identify duplicate entries

Good Afternoon,   I hope someone can help. It is a bit confusing but I will try to explane to the best my ability.   I have unique activities which were raised and completed between certain times...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    Try this formula, please.

    Column =
VAR dupCount =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && Table1[Completed Date] = EARLIER ( Table1[Completed Date] )
                && Table1[Activity] <= EARLIER ( Table1[Activity] )
        )
    )
VAR lastRaisedDate =
    CALCULATE (
        MIN ( Table1[Raised Date] ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && 'Table1'[Activity]
                    = EARLIER ( Table1[Activity] ) - 1
        )
    )
RETURN
    IF (
        [Raised Date] <= [Completed Date]
            && [Raised Date] >= lastRaisedDate
            && dupCount > 1,
        "Y",
        "N"
    )

    Identify-duplicate-entries

     

    Best Regards,
    Dale

  • Anonymous's avatar
    Anonymous
    7 years ago

    Thank you so much for this. However, when i enter the formula i get this error message. I cannot work it out why tho..

     

    Thank you

     

     

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

    What's the Data type of [Activity]? It should be a numeric type to work with the formula. Can you change it or share a more accurate sample, please?

     

     

    Best Regards,
    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

Try this formula, please.

Column =
VAR dupCount =
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && Table1[Completed Date] = EARLIER ( Table1[Completed Date] )
                && Table1[Activity] <= EARLIER ( Table1[Activity] )
        )
    )
VAR lastRaisedDate =
    CALCULATE (
        MIN ( Table1[Raised Date] ),
        FILTER (
            'Table1',
            Table1[Post code] = EARLIER ( Table1[Post code] )
                && Table1[Street] = EARLIER ( Table1[Street] )
                && 'Table1'[Activity]
                    = EARLIER ( Table1[Activity] ) - 1
        )
    )
RETURN
    IF (
        [Raised Date] <= [Completed Date]
            && [Raised Date] >= lastRaisedDate
            && dupCount > 1,
        "Y",
        "N"
    )

Identify-duplicate-entries

 

Best Regards,
Dale

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you so much for this. However, when i enter the formula i get this error message. I cannot work it out why tho..

 

Thank you

 

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    What's the Data type of [Activity]? It should be a numeric type to work with the formula. Can you change it or share a more accurate sample, please?

     

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I really appreciate your help! Although the activity was a combination of numbers and letters i incuded Index under the query. So it all works now.

       

      If not too much to ask i have another question/task based on this excersise. In the example i used only a date but in the practical excersise i also need to calculate completion time since previous completed acitvity for the same post code and address. I am attaching an example and how it would work in excel.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      I am sorry, but it is me again. Your formula was great, but it brought up another few issue,s after i started using big data set.

       

      As i mentioned i included index but it didn't seem to resolve the issue for THE bigger data set.

       

      So i will use another example to explain:

       

       I need to identify how many duplicated activities there are raised for the same post code and street within the last 24 hours since completion date.  However, first activity after previous activity was completed wouldn't count as duplicate as it is classed as a new entry, but any other activity after would be duplicate.

       

      The formula which i use don't seem to like that completion date is the same hours and seconds. But also, includes some of activities, whihc are completed at the same time but the first activity was raised later then second one.

       

      Worth to mention that i sorted the data in advance editor :  #"Sorting" = Table.Sort(#"Changed Type",{{"Postcode", Order.Ascending}, {"CompletedDate", Order.Ascending},{"Street", Order.Ascending}})

       

      Also,I included Index based on post code and used the below formula in Report tab and not Data  . Example data attached.

      ActivityRaisedDateCompletedDateStreetPostcodeOpsRegion_Join
      0123 456ABCD01/01/1980 15:1902/01/1980 12:00Little streetAB1 2CDEastern South
      0123 456BCDE01/01/1980 15:2602/01/1980 12:00Little streetAB1 2CDEastern South
      0123 456CABFE01/01/1980 15:5202/01/1980 12:00Little streetAB1 2CDEastern South

       

      Duplicated(24hrs) =

      var dupCount = CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Postcode] = EARLIER(Table1[Postcode])&& Table1[Street]=EARLIER(Table1[Street])&& Table1[CompletedDate]>=EARLIEr(Table1[CompletedDate])-1&&Table1[CompletedDate]=Table1[CompletedDate] && Table1[CompletedDate]<=EARLIER(Table1[CompletedDate])&& Table1[Index PostCode]=Table1[Index PostCode] &&Table1[Index PostCode]<=EARLIER(Table1[Index PostCode])))

      var lastraiseddate = CALCULATE(MIN(Table1[RaisedDate]),FILTER(Table1,Table1[Postcode]=EARLIER(Table1[Postcode])&& Table1[Street]=EARLIER(Table1[Street])&& Table1[Index PostCode]=EARLIER(Table1[Index PostCode])-1))

      RETURN IF(Table1[RaisedDate]<=Table1[CompletedDate]&& Table1[RaisedDate]>=lastraiseddate&& dupCount>1,"Y","N")

       

      Thank you so much for your help in advance.

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        Hi Anonymous ,

         

        I would suggest you create a new thread in this forum. 

         

        Best Regards,