Forum Discussion

abdalla's avatar
abdalla
Helper I
4 years ago
Solved

duplicates

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...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi abdalla 
Which two shall be kept?

abdalla's avatar
abdalla
Helper I
4 years ago

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.