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

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.

brinky's avatar
brinky
Helper IV
3 years ago

Any idea why this isn't working and when I add [Expiry_date] is works seemlesly

 

 

  • mattlancs's avatar
    mattlancs
    Advocate II
    3 years ago

    Hi Brinky,

    I've had a play, and changing your formula to this should fix it:

    = Table.ReplaceValue(#"Filtered Rows",
    each [Route_Description],
    each if [Route_No] = 125 then "Route" & Text.From([Route_No]) else [Route_Description],
    Replacer.ReplaceText,{"Route_Description"})

    It seems that where you're combining the text "Route" with a field you need to explicitly tell it that you're combining text with text.

     

    Cheers,

    Matt