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""

mattlancs's avatar
mattlancs
Advocate II
7 years ago

Hi Cuiping,

 

As far as I understand it, the bits before the = sign are just giving that line a name. So in this case it's saying "for my next trick I will perform an action called Replaced OTH". After the = sign, you get the name of the function that's being applied in this line, and then the first bit in the bracket is the name of the previous line. I presume this is so that it knows which order to run the lines in, somehow, but it can make it confusing to read at first.

 

You can change the names of the lines - Replaced OTH for example isn't a default sort of name, but it would have made the list of actions easier to read for its author - however if you're doing that in the editor you have to also remember to to change the name where it's mentioned in the next line, too.

 

Good luck!

 

Cheers,

Matt