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.

vaibhav_osc's avatar
vaibhav_osc
Advocate I
8 years ago

Is there a way to do the same for multiple columns at once? 

I need to update entire row as #NA if a certain value is found in a column

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

The last argument {"Gender"} is the list of columns in which values must be replaced. If you first select the applicable columns, and then choose for Replace Values, the generated code will include the names of the selected columns in the last argument.