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])))

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: