Forum Discussion

asodhani's avatar
asodhani
Frequent Visitor
8 years ago
Solved

How to create new column based on duplicate values

I want to create a column Duplicate Contracts . I use to do this in excel by   B3 and B2 are row numbers for Contract Number for column.   =IF((B3-B2)=0,"Duplicate","Not Duplicate")   I cannot ...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Then just change the code from the original calculation I sent to use the name of the index column you just added where I have highlighted below.

     

     

    Column = 
VAR CountOfRows = 
    CALCULATE(
            COUNTROWS('Table1'), 
            FILTER( 'Table1' , 
            'Table1'[Contract Number] = EARLIER('Table1'[Contract Number]) && 
            'Table1'[Index Column] > EARLIER('Table1'[Index Column])
            )
            )+0
RETURN IF(CountOfRows=0,"Not Duplicate","Duplicate")

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

HI asodhani

 

This calculated column should work, but it uses the [Contract Value] column to work out which is the first (non duplicate) rows.  Do you have a Date/Time column in your data that could be used instead?

 

Column = 
VAR CountOfRows = 
    CALCULATE(
            COUNTROWS('Table1'), 
            FILTER( 'Table1' , 
            'Table1'[Contract Number] = EARLIER('Table1'[Contract Number]) && 
            'Table1'[Contract Value] > EARLIER('Table1'[Contract Value])
            )
            )+0
RETURN IF(CountOfRows=0,"Not Duplicate","Duplicate")
  • asodhani's avatar
    asodhani
    Frequent Visitor
    8 years ago

    Hi Phil,

    Thanks for the feedback. The contract value, date and time of creation are all exactly the same.

     

    The table I presented should have looked like below. My mistake I added contract value incorrectly.

     

    Contract Number

    Contract Value

    Duplicate Contracts

    1000

    100000

    Not Duplicate

    1001

    1000000

    Not Duplicate

    1001

    1000000

    Duplicate

    1002

    10000

    Not Duplicate

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      8 years ago

      Hi asodhani

       

      Do you have any other columns that can be used to split the tie for the 1001 record?  

       

      • asodhani's avatar
        asodhani
        Frequent Visitor
        8 years ago

        Phil_Seamark

         

        Hi Phil,

         

        There are no other columns which have different value. Its a exact replica.

         

        Cheers

         

        Abhi