Forum Discussion

jonbox's avatar
jonbox
Helper II
3 years ago
Solved

Compare rows and delete based on condition

Hi All, hopefully a simple request;

 

I have data similar to Table1 where the people in the "Name" column are in different "rooms". What i want is to get to the Table2 where if they're in room 5 specifically, remove all other rooms associated to their name e.g. for Jon, he's in room 5 so remove room 8, same for Ben where because he's in Room 5, remove room 6 and 3, whereas for Dan because he's not in room 5, keep Room 2 and Room 9.

 

Can anyone help on a formula where you look at all the rows per person, check if room 5 is there, if so delete every row except the one with room 5. If a person doesn't have a row with room 5, keep all the rows.

 

Hopefully that's clear! Thanks!

 

Table1:

NameRoom
JonRoom 5
JonRoom 8
BenRoom 6
BenRoom 3
BenRoom 5
AlexRoom 8
AlexRoom 5
AlexRoom 2
DanRoom 2
DanRoom 9

 

Table2:

NameRoom
JonRoom 5
DanRoom 5
AlexRoom 5
BenRoom 2
BenRoom 9
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, jonbox 

     

    You can try the following methods.

    Column:

    Check Room 5 = 
Var _table=CALCULATETABLE(VALUES(Table1[Room]),FILTER(ALL(Table1),[Name]=EARLIER(Table1[Name])))
Var _N1=IF("Room 5" in _table,"Room 5",[Room])
Return
IF([Room]=_N1,[Room],BLANK())

    New Table:

    Table2 = FILTER(Table1,[Check Room 5]<>BLANK())

    Is this the result you expect?

     

    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.

1 Reply

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, jonbox 

     

    You can try the following methods.

    Column:

    Check Room 5 = 
Var _table=CALCULATETABLE(VALUES(Table1[Room]),FILTER(ALL(Table1),[Name]=EARLIER(Table1[Name])))
Var _N1=IF("Room 5" in _table,"Room 5",[Room])
Return
IF([Room]=_N1,[Room],BLANK())

    New Table:

    Table2 = FILTER(Table1,[Check Room 5]<>BLANK())

    Is this the result you expect?

     

    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.