Remove duplicate text values across multiple columns

[Quenril]

Hello!

New Power BI user here and struggling a bit with cleaning my data... I suspect this may be an easy one for you all, so please help if you can..?

I have attached a screenshot of my data table (each letter represents a Consultant name). I need to de-duplicate the names in the table so that the name in the Lead Consultant column doesn't recur in Attendee columns 1-3.  

Current dataset

So my ideal solution would look like this:

Dataset solution

 Thank you so much!

[Quenril]

@Anonymous and @PijushRoy thank you both so much. I did consider conditinoal columns but it felt like I was simply duplicating data so I thought I must be missing something. Thank you for confirming that this is the best approach 😁

[PijushRoy]

Thanks @Quenril 
After posting, saw @Anonymous already posted the same solution.

Pijush

[Anonymous]

@Quenril  You're welcome

[PijushRoy]

Hi @Quenril 

Please create  conditional column, then delete  original column

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS

Thanks
Pijush

[Anonymous · Accepted solution]

@QuenrilYou can go into the Query Editor and create 3 new Consultant Attendees conditional columns. I've shown one below as an example. Once all the new Consultant Attendees columns have been created, you can remove the 3 original ones and rename the new ones to what they should be.

Final result: