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.

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

Edit: wrong answer:

 

You can't replace values in a column based on values in another column.

 

Instead, create an additional column and replace the existing column with the new column.

 

Adjusted part of the code:

 

    #"Replaced OTH" = Table.ReplaceValue(#"Promote Header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
    #"Added Custom" = Table.AddColumn(#"Replaced OTH", "Custom", each if Text.Contains([Surname],"Manly") then "Male" else [Gender]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Gender"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Gender"}})
in
    #"Renamed Columns"