Query Editor - replace multiple values

[Anonymous]

Hello -  Our ERP system captures input for who took an order....but the input is the person's initials.  

I have about 40 of these two change....but don't want a giant list of "Replaced Value" in my query editor steps.    Here are the first two, but I am not sure how to apply all of these steps into ONE step in the advanced query editor?    

= Table.ReplaceValue(#"Renamed Columns","GB","GREG BROWN",Replacer.ReplaceText,{"Taken By"})

= Table.ReplaceValue(#"Replaced Value","CD","CHARLIE DAVIS",Replacer.ReplaceText,{"Taken By"})

[vivekhn]

Hello,

You can use the if else statements to replace multiple categories in a single power query replace value formula.
For example, If you have a status column with the values A,I,T and wish to replace them with Active, Inactive, and Terminated use the formula provided below.

= Table.ReplaceValue(
#"Filtered Rows", 
each [Status],
each if [Status] = "A" then "Active" else if [Status] = "I" then "Inactive" else "Terminated",
Replacer.ReplaceValue,{"Status"}
)

[Anonymous · Accepted solution]

Hi @Anonymous ,

How about creating a custom column using if else function?

if [initials] = "GB" then "GREG BROWN" else if [initials] = "CD" then "CHARLIE DAVIS" else ""

Best Regards,

Jay

[Ashish_Mathur]

Hi,

Create a seperate 2 column table with Initials in the first column and Name in the second column.  Then use the Merge tables functionality to bring over Name from this new table into your existing one.  From your existing table, you may then remove the Initials column.

[Anonymous]

Hi @Ashish_Mathur   This implies setting up a standalone table.  

I am changing the initials, which are part of our ERP system's input.    User's select the "Taken By"  column and choose from a drop down to select their initials.  

By changing the values in this column from initials, to full name, I can make sure the original column from the data source is updated.  I don't really want to use a standalone table.  

[amitchandak]

@Anonymous , refer

https://www.howtoexcel.org/power-query/bulk-replace-values/

https://community.powerbi.com/t5/Desktop/Power-Query-Replace-multiple-substrings-in-one-column/td-p/409573

[Greg_Deckler]

@Anonymous - Wait, what do you mean updating the original data source? Using @Ashish_Mathur method you can remove and rename columns and such to make things like the original Source data.

Maybe I'm not clear on what you are trying to achieve exactly but @Ashish_Mathur said exactly what I would have said.