cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Employee

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")```

Proud to be a Datanaut!

8 REPLIES 8
Employee

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")```

Proud to be a Datanaut!

Frequent Visitor

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
Employee

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

Proud to be a Datanaut!

Frequent Visitor

@Phil_Seamark

Hi Phil,

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

Cheers

Abhi

Employee

This will be an issue for DAX.  If the data in all columns is identical for multiple rows, the calculation will return the same.

Can you add an Index column to the data in the query editor?  This will at least provide something for DAX to work with.

Proud to be a Datanaut!

Frequent Visitor

I added a Index Column now. Please let me know if you have any further thoughts.

Thank you for your help to date.

Cheers

Employee

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")```

Proud to be a Datanaut!

Frequent Visitor

Its working for me now.

Cheers

Abhi

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors