Forum Discussion
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 use above in Power Bi so wondering what,s my best option? Please advise.
Please note 1001 is repeated 2 times and I want the first one to be stated as NOT DUPLICATE and others as DUPLICATE
Contract Number | Contract Value | Duplicate Contracts |
1000 | 100000 | Not Duplicate |
1001 | 1000000 | Not Duplicate |
1001 | 1000 | Duplicate |
1002 | 10000 | Not Duplicate |
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")
8 Replies
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")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
