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.

LSM's avatar
LSM
Regular Visitor
8 years ago

Hi mattlancs

 

I hope it's okay to ask another question to this old post. I tried to use your example to replace any value over multiple columns based on a criteria but can't seem to get it to work.

 

 

So - in your example. What if you wanted to replace not only null but any value from "SP Status" and "TS Status" based on your criteria. 

 

Thanks in advance

Lars

mattlancs's avatar
mattlancs
Advocate II
8 years ago

Hi Lars,

 

Sorry for the slow reply I only stumbled upon your question when I found this thread looking up the same problem again!

 

I've wrestled with this for a while but can't get it to work for more than one column. To do one column, replacing the null with each [#"TS opps.Status"] eventually worked. But I don't know how to refer to 'every value in either column', which seems like it should be straightforward. Then again I still don't understand what that little # adds to the previous example...

 

Apologies I can't give you more help - hopefully you're well past the problem by now anyway.

 

Matt