duplicates

[abdalla]

hello im using power query and i have a big set of data where the names in my list sometimes repeat up to 6 times.

My question is, Is there anyway that i can remove 4 of them and keep only 2 or if i remove duplicates than all will be gone ,

I need the calculation done to them.

[tamerj1]

Hi @abdalla 
Which two shall be kept?

[abdalla]

Hi  @tamerj1 

the original data set didnt have any coloumn to the right of the string column, I am calculating the string and well name from the well column and reservior column

You will find that some wells are the exact same and have the same reservior number so my request is how do i formulate it so that excel only takes one of these entries. 

[tamerj1]

@abdalla 
In this case if you select all the columns from "Well" to "Reservoir" then "Remove Duplicates" will keep 2 rows.

[abdalla]

im only looking for to keep 2 of the duplicates because the duplication comes in the well column but when it comes to the reservoir no. column i need to keep 2 numbers there, 

i tried the remove duplicates before and it literally removed them i need to keep them but not calculate them.

[v-zhangti · Accepted solution]

Hi, @abdalla 

You can try to solve this problem with Dax in desktop.

Column:

Rank = 
RANKX(FILTER('Table',[Well]=EARLIER('Table'[Well])),[Row],,ASC)

Table:

Table 2 = 
Var _table=
SUMMARIZE('Table','Table'[Row],'Table'[Well],'Table'[WHAT],'Table'[Rank])
return
FILTER(_table,[Rank]<=2)

Does this meet your desired outcome?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.