Forum Discussion
Replace multiple values in columns without creating new columns
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!!!
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/409573
4 Replies
- johncolley
Solution Sage
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!
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
- johncolley
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/409573
- v-xiaosun-msft
Community Support
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 _ xiaosunIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
