Forum Discussion

Decal's avatar
Decal
Helper I
2 years ago
Solved

URGENT: Single Cell Date Replace

I'm doing HR reporting where Hire Date is an important field.  

 

A person was rehired under the same employee ID causing retention reporting to be thrown off.

 

The employee id is 07385.  The current Hire Date is 04/15/2024 (yes, I know forward dated).  I need it to be 09/28/2020.

 

I tried using this:

#"HireDtKaylee" = Table.ReplaceValue(#"Replaced Value2", "07385", each if [EEID]="07385" then #date(2020,9,28) else #date(2024,4,15),Replacer.ReplaceText,{"Hire Date"})

 

But it did not update the date.  Can someone please help?

Need Help
  • ronrsnfld's avatar
    ronrsnfld
    2 years ago
    #"HireDtKaylee" = Table.ReplaceValue(
        #"Replaced Value2",
         each [EEID] = "07385",
         #date(2020,9,8),
         (x,y,z)=> if y = true then z else x,
         {"Hire Date"})

5 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago
    #"HireDtKaylee" = Table.ReplaceValue(
        #"Replaced Value2",
         each [EEID] = "07385",
         #date(2020,9,8),
         (x,y,z)=> if y = true then z else x,
         {"Hire Date"})
    • Decal's avatar
      Decal
      Helper I
      2 years ago

      I'm unclear how to use the x,y,z.  Can you clarify how to use the eeid and dates in here?

      • Decal's avatar
        Decal
        Helper I
        2 years ago

        Scratch that, I did it verbatim and it works.  I guess somehow it knows what x, y, and z are