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,{"Gender"}),
#"Replaced Gender" = Table.ReplaceValue(#"Replaced OTH",each if [Surname] = "Manly" then "Male" else [Gender],[Gender],Replacer.ReplaceValue,{"Gender"})

 

in
#"Replaced Gender"

 

Im trying to change the gender column to "Male" if Surname column contains "Manly". It gives me an error:

 

Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

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.

34 Replies

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    4 years ago

    Marcel Beug gave a great solution there. For your reference, I wrote an elaborate guide on replacing values based on conditions. Also including capital insensitive replacements. The general construct is: 

     

     

     

    = Table.ReplaceValue(
     #"Changed Type",
     each [Gender],
     each if [Surname] = "Manly" then "Male" [Gender] ,
     Replacer.ReplaceValue,{"Income"}
 )

     

     

     

    https://gorilla.bi/power-query/replace-values/

     

    Hope that helps!

     

    Rick

     

    --------------------------------------------------

    @ me in replies or I'll lose your thread

     

    Master Power Query M? -> https://powerquery.how

    Read in-depth articles? -> BI Gorilla

    Youtube Channel: BI Gorilla

     

    If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

  • ClackamasAshley's avatar
    ClackamasAshley
    Frequent Visitor
    2 years ago

    Hi There,

     

    I am having an issue that I think can be solved by the same method, I'm just not quite there yet and would appreacite some help if possible.

     

    I have police call data, and am trying to caluclate response times for calls that were requested by the public. My dataset has both public-requested, and deputy-inititated calls (like traffic stops). Deputy-Inititated calls dont have response times since they are self-inititated. The issue is, sometimes a response time is recorded in error in the data collection process based on the caputed timestamps for a deputy-inititated call, which makes no sense, but it's there nonetheless so I need to eliminate them from the actual public-dispatched response times data. I don't want to filter these calls out completely, as I still need the accurate call counts overall. I need to change all response times for these deputy-inititated calls to "0" mins so they don't affect my dispatched response averages. 

     

    So my condition is: IF [Inititated Method] = "Self-Inititated" then [Response Time (dec. mins)] = 0, else [Response Time (dec. mins)].

     

    Inititation Method is a text-type column, and Response Time (dec. mins) is a decimal number-type column.

     

    I tried to create a new "Accurate Response Time" column in Power Query using this condition, but I can't get it to stick. Using examples from this thread I came up with this, but it's not working. Any ideas? Much appreacited!

     

    = Table.ReplaceValue(
    #"Changed Type",
    each [#"Response Time (dec. mins)"],
    each if [Initiation Method] = "Self-Initiated" then 0 [#"Response Time (dec. mins)"]
    Replacer.ReplaceValue,{"Response Time (dec. mins)"}
    )

     

     

     

    Ashley

     

    • mattlancs's avatar
      mattlancs
      Advocate II
      2 years ago

      Hi Ashley,

      You're really nearly there! So, the approach I was discussing in this thread was about replacing values in an existing column without adding any more, while you were trying to add a new column to supersede the one you've got.
      To take the original approach which keeps it all tidily within the existing columns, I'll give you the dumb-and-clumsy approach which I always take:

      • Right click on your Response Time column header, select "Replace values", and it gives you a prompt for what values you want to replace. Say you want to find 1, and replace it with 2 - this is just to get a line added to the code which you can then finesse later.
      • In the formula bar at the top of the screen, you'll hopefully then see something like 

       

      = Table.ReplaceValue(#"Changed Type",1,2,Replacer.ReplaceValue,{"Response Time (dec. mins)"})​

       

      • You can then edit that line, and change it to basically what you'd written above, though with an added comma and an 'else':

       

      = Table.ReplaceValue(#"Changed Type", each [#"Response Time (dec. mins)"], each if [Initiation Method] = "Self-Initiated" then 0 else [#"Response Time (dec. mins)"], Replacer.ReplaceValue,{"Response Time (dec. mins)"})​

       

      Hopefully that'll do it. Otherwise, if you want to tackle the issue by adding a new "Accurate response time" column then go to the Add Column tab at the top of the page, select Conditional Column, and fill it out like this:

       

      Cheers,

      Matt

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    Edit: wrong answer:

     

    You can't replace values in a column based on values in another column.

     

    Instead, create an additional column and replace the existing column with the new column.

     

    Adjusted part of the code:

     

        #"Replaced OTH" = Table.ReplaceValue(#"Promote Header"," ","OTH",Replacer.ReplaceValue,{"Gender"}),
    #"Added Custom" = Table.AddColumn(#"Replaced OTH", "Custom", each if Text.Contains([Surname],"Manly") then "Male" else [Gender]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Gender"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Gender"}})
in
    #"Renamed Columns"

     

  • 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!

    • 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

  • fra123's avatar
    fra123
    New Member
    2 years ago

    I am new to this, thank you in advance for your answers. 

     Have a dataset with 3 columns: Amount (decimal numbers positive and nebative values) - Id(several duplicate values - Type(a,b,c,d).

    I just need to change values on amont upon conditions:

    for type == "d" and amount <0 then change the amount into positive (abs value).

    Is that possible? it seems a similar case to yours.

     

    Thank you 

    F.

    • mattlancs's avatar
      mattlancs
      Advocate II
      2 years ago

      Hi F,

       

      The code would be something like:

       

      = Table.ReplaceValue(#"Changed Type", each [Amount], each if [Amount] <0 and [Type] = "d" then Number.Abs([Amount]) else [Amount], Replacer.ReplaceValue,{"Amount"})

       

      The safest/easiest way to add that (in my experience) is to do a "Replace Values" function on the column, by highlighting it and either choosing the option from the Transform tab at the top or by right-clicking on the column heading and choosing it. Pick something like replacing 1 with 2, you just want to get the fundamental line of code in there. Then edit that line to replace that "what am I replacing" and "what am I replacing it with" bit, by pasting in the section in bold above.

      Good luck!

       

      Cheers,

      Matt

      • fra123's avatar
        fra123
        New Member
        2 years ago

        Hi Matt,

         

        That's brilliant, thank you so much. It worked great. I hope I will get to learn more about coding in Power Query