Forum Discussion

salgouda's avatar
salgouda
Frequent Visitor
3 years ago
Solved

Power Query replace column value based on another column

Hello there,

 

I am calculating employee retention and I need to change the null values in isActive column to be "false" if there's an end date which means the employee left, and "true" if end date is null which means the employee is still active.

 

 

this is the code I am using:

 

#"Replaced Value" = Table.ReplaceValue(#"Replaced OTH",each [isActive],each if [End Date] is null then "true" else [isActive],Replacer.ReplaceValue,{"isActive"})

 

but it doesn't seem to work.

 

Any tips? 

Need Help

3 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi salgouda ,
    if you enter an equal sign just at the start, the formula should work.

  • ImkeF's avatar
    ImkeF
    Community Champion
    3 years ago

    Hi salgouda ,
    the syntax looks correct. But the screenshot you have posted doesn't match your formula (-"...Copy" columns).
    Otherwise: What does the error message says or what exactly is wrong with the current result you are getting?

    • salgouda's avatar
      salgouda
      Frequent Visitor
      3 years ago

      Hi ImkeF 

       

      the original columns are just named isActive, and End Date

      When I try to add a new custom step to write this code nothing happens!

       

      it seems like I am missing something but can't figure out what is it?