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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

mattlancs 

Hey Matt,

I'm getting an error I don't see anywhere else in this thread.

 

I'm applying the solution as follows:

= Table.ReplaceValue(#"Replaced Value1", each [mycode], each if [othercode] = "K0606" then "RR" else [mycode],Replacer.ReplaceValue{"mycode"})

 

And it is returning this error:

Expression.Error: We cannot apply indexing to the type Function.
Details:
Value=[Function]
Index=mycode

 

Any idea how to resolve this?

 

Thanks,

Sean

mattlancs's avatar
mattlancs
Advocate II
6 years ago

Anonymous 

 

Hiya,

 

It looks like there's a comma missing after the last ReplaceValue. Fingers crossed that's all it is!

 

= Table.ReplaceValue(#"Replaced Value1", each [mycode], each if [othercode] = "K0606" then "RR" else [mycode],Replacer.ReplaceValue,{"mycode"})

 

Cheers,

Matt