Forum Discussion
Query Editor - replace multiple values
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"})
6 Replies
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.
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.
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.
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"}
)
