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.

cuiping's avatar
cuiping
Frequent Visitor
7 years ago

sorry i am a beginner study from scratch, may i know what this for..."#"Replaced OTH""

KAURM's avatar
KAURM
Helper I
5 years ago

Hi cuiping the #Replaced OTH stands for the name of the step for which the  statement is being applied to.

 

You can see from below after #promote header the statement that has been applied to this dataset has been named #replaced OTH and the #Replaced Gender statement is applying those power query statements using both those datasets. 

 

#promote header = ....

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

 

Hope that makes sense!