Forum Discussion

spoony's avatar
spoony
Helper I
9 years ago
Solved

Query editor replacing values based on another column

I can't seem to get this to work in query editor:   let Source = Excel.Workbook #promote header = .... #"Replaced OTH" = Table.ReplaceValue(#"promote header"," ","OTH",Replacer.ReplaceValue,{"Ge...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    You are right. I was confusing Table.ReplaceValues with Table.TransformColumns. :smileyembarrassed:

    My solution works though, but the code you are looking for:

     

        #"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [Gender],each if [Surname] = "Manly" then "Male" else [Gender],Replacer.ReplaceValue,{"Gender"})

     

    Edit: it seems you switched "old"  and "new"  in your cide.

Rickmaurinus's avatar
Rickmaurinus
Helper V
4 years ago

Marcel Beug gave a great solution there. For your reference, I wrote an elaborate guide on replacing values based on conditions. Also including capital insensitive replacements. The general construct is: 

 

 

 

= Table.ReplaceValue(
     #"Changed Type",
     each [Gender],
     each if [Surname] = "Manly" then "Male" [Gender] ,
     Replacer.ReplaceValue,{"Income"}
 )

 

 

 

https://gorilla.bi/power-query/replace-values/

 

Hope that helps!

 

Rick

 

--------------------------------------------------

@ me in replies or I'll lose your thread

 

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

 

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