Forum Discussion

Dbjerring's avatar
Dbjerring
Regular Visitor
9 years ago
Solved

Remove duplicates based on values from multiple cells

Hi Community,   We have dataset based on logs - here we would like to create a new column with a true/false value if the line is a duplicate. We have UserID, LogTimeDate, LogTime, and number of lo...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    Dbjerring,

     

    You could modify the formula as shown below.

    IsDuplicate =
IF (
    COUNTROWS (
        FILTER (
            Logs,
            Logs[LogCNumber] = EARLIER ( Logs[LogCNumber] )
                && Logs[LogTime.1] = EARLIER ( Logs[LogTime.1] )
                && Logs[LogID] < EARLIER ( Logs[LogID] )
        )
    )
        > 0,
    TRUE (),
    FALSE ()
)
Dbjerring's avatar
Dbjerring
Regular Visitor
9 years ago

Anonymous - No, I still hope he'll reply.

 

Do you have any inputs?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Dbjerring

 

Yea I have a solution in mind. Could you create a sample dataset with 5 rows? I'd like to test it before suggesting it.

  • Dbjerring's avatar
    Dbjerring
    Regular Visitor
    9 years ago

    Anonymous - The dataset: 

     

    LogIDLogCNumberLogTime.1LogCountIsDuplicate
    2123456720-06-2017 FALSE
    3117269020-06-2017 FALSE
    4 21-06-2017-1FALSE
    5114524321-06-201772TRUE
    6114524321-06-201772TRUE
    7114524321-06-201772TRUE
    8117269028-06-201794FALSE
    9117269030-06-201755FALSE
    10114524307-07-2017115TRUE
    11114524307-07-2017115TRUE
    1229745812-07-201733FALSE
    13106157113-07-2017163FALSE
    1433887313-07-201714FALSE
    1546973613-07-20179FALSE
    16116681813-07-2017148FALSE
    17110724513-07-201724TRUE
    18110724513-07-201724TRUE
    19116536913-07-201751FALSE
    20111786713-07-201722FALSE

     

    Thank you

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    Dbjerring,

     

    You could modify the formula as shown below.

    IsDuplicate =
IF (
    COUNTROWS (
        FILTER (
            Logs,
            Logs[LogCNumber] = EARLIER ( Logs[LogCNumber] )
                && Logs[LogTime.1] = EARLIER ( Logs[LogTime.1] )
                && Logs[LogID] < EARLIER ( Logs[LogID] )
        )
    )
        > 0,
    TRUE (),
    FALSE ()
)
  • Dbjerring's avatar
    Dbjerring
    Regular Visitor
    9 years ago

    v-chuncz-msft: I get a "Too many arguments were passed to the IF function. The maximum argument count for the function is 3.":

     

    IsDuplicate = 
IF (
    COUNTROWS (
        FILTER (
            Logs;
            Logs[LogCNumber] = EARLIER ( Logs[LogCNumber] )
                && Logs[LogTime.1] = EARLIER ( Logs[LogTime.1] )
                && Logs[LogID] < EARLIER ( Logs[LogID] )
        )
    )
        > 0;
    TRUE ();
    FALSE ();
)

    Any thoughts?

     

    Thank you so much for the help

     

    //David

     

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    Dbjerring,

     

    Remove the last semicolon after "FALSE ()".

  • Dbjerring's avatar
    Dbjerring
    Regular Visitor
    9 years ago

    v-chuncz-msft - Thank you so much - That did the trick.

     

    Have a nice day!

  • Asantos2020's avatar
    Asantos2020
    Advocate II
    7 years ago

    Hlelo v-chuncz-msft ,

     

    How would you go about identifying and keeping the latest row from multiple files datasource containing duplicates?

     

    Cheers,

    Antonio Santos