Replace multiple values in columns without creating new columns

[elippe]

Hi, 

I am trying to figure out how to replace values in a column with corrisponding values on another table. I know the easiest way is a merge query. However, that will cause a lot of problems with my layout that I already have set up. 

I have a table (Business Clients) with servicing roles that are populated with codes. I have a seperate table (Employee) that has the same code and a new code that I need. You can see my images below. I need to replace the servicing role codes on Business Client with those from Employee (Epic Code). I am going to have to do this for multiple columns on Business Client (hence why I don't want to do it as a merge query).  Please help me!!!! If it were excel I would just do a vlookup but I can't here. I do not want to add any columns or change my layout in any way. 

 

Thank you for any help!!! 

[elippe]

Hi, 

Yes I know about this function, but I was hoping there was a dax formula. There is about 50 values I have to replace in each column and there are at least 10 columns I have to have these values replaced in. 

Thanks, 

Elise

[v-xiaosun-msft]

Hi @elippe ,

Unfortunately, as @johncolley mentioned, when you use dax, it will create a new column. It is because that we use dax to create a calculated column, a measure or a table. If you want to replace multiple values in columns without creating new columns, you need to do it in Power Query.

Best Regards,
Community Support Team _ xiaosun

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

[johncolley · Accepted solution]

Hi @elippe , Dax has replace and substitute functions - but anything you do with dax will result in a new column. 

There are several power query solutions provided here that you may be able to replicate:  https://community.powerbi.com/t5/Desktop/Power-Query-Replace-multiple-substrings-in-one-column/m-p/4... 

[johncolley]

Hi @elippe , In Power Query you could use the replace values option - however depending on the number of different values you are needing to change it may be labour intensive 

If this answer solves your problem please mark it as a solution!