In this article, I will show you how to identify unique and duplicate values:

In my table, INC001, INC003 and INC005 have duplicates so to get only one of them, based on what I want, I will need either the index, number or update column. The formula is the same for all of them except for argument2:

var dupuni='table'[argument1]
Return
IF(RANKX(FILTER(ALL('table'),'table'[argument1]=dupuni),[argument2],,ASC)>=2,0,1)

NOTE:

• Change table and argument by yours
• Change ASC by DESC based on the argument2

With the index column:

NOTE: argument2 is ASC because I want the first index number

With the number column:

NOTE: argument2 is DESC because I want the highest number so if I want the lowest one, I will use ASC

With the date column:

NOTE: argument2 is DESC because I want the last date

You may be interested by this related article Power BI: compare tables for new data or duplicate

To count duplicate, use this formula:

COUNTROWS(FILTER('table','table'[argument1] = EARLIER('table'[argument1])))

With this last formula, I will be able to show if duplicate or unique:

To know where are the duplicates based on the index column, use this formula:

var dupuni = 'table'[argument1]
Return
IF([argument3]>1,CONCATENATEX(FILTER(ALL('table'),'table'[argument1]=dupuni),'table'[argument2],", ",[argument2],ASC))

Since the duplicates have different numbers, I want to get the unique number based on the last update:

var dupuni='table'[argument1]
var xdup=IF(NOT(ISBLANK([argument1])),COUNTROWS(FILTER('table','table'[argument1] = EARLIER('table'[argument1]))))
var maxupd=IF(NOT(ISBLANK(dupuni)) && xdup>1,CALCULATE(MAX('table'[argument4]),FILTER('table','table'[argument1]=dupuni)))
Return
IF(NOT(ISBLANK(dupuni)),IF(xdup>1,CALCULATE(MAX('table'[argument5]),FILTER('table','table'[argument1]=dupuni && 'table'[argument4]=maxupd)),[argument5]))

I will create 2 measures:

• measure, to count how many duplicates:
  var numtkt = ADDCOLUMNS(VALUES('table'[argument1]),"dupuni", CALCULATE(COUNTROWS('table')))
  Return
  COUNTROWS(FILTER(numtkt,NOT(ISBLANK('table'[argument1])) && [dupuni]>1))
  
• measure 2, to count how many tickets:
  COUNTROWS(FILTER(VALUES('table'[argument1]),NOT(ISBLANK('table'[argument1]))))
  

In the other hand, if I want to remove duplicates in Power Query, I will have to duplicate my table if I want to use a condition other than index. In Power Query Editor, select the column then click on “home -> remove rows -> remove duplicates”:

To remove them based on criteria, I will have to duplicate my table called “table2”:

I will only show how to do it with the date and not with number because it is the same process for both. On “table1”, I will select the column then click on “home -> group by”:

On the popup, fill the fields:

• new column name: put whatever I want
• operation: I select “max” to get the last date but if I want the first date, “min” has to be selected
• column: select the date column

This is the result:

Click on “home -> merge queries”:

On the popup, for both tables, select both columns:

Click on the expand icon to select the missing columns:

The final result: