Forum Discussion

Arnault_'s avatar
Arnault_
Resolver III
6 years ago
Solved

Replace dates lower than a specific date by null

Hi All,

I am trying to replace dates in my dataset. I need to replace every date lower than 2015.1.1 by null.

The code works fine and I would like to extend the function to multiple columns. In my current scenario, these columns are the following"TS01_stock_take_date" and "TS02_creation_date".

Thanks in advance for your advise.

Cheers

 

= Table.ReplaceValue(#"Merged Columns", each [TS01_stock_take_date] , each if [TS01_stock_take_date] < #date(2015,1,1) then null else [TS01_stock_take_date],Replacer.ReplaceValue,    {"TS01_stock_take_date"})

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Arnault_ ,

     

    I guess, one option would be to replace value on the other tab as a separate (second) step.

     

    If you something like "mass" change, you need to replace the standard Replacer function, and pass a list of columns to apply to.

    Replacer function takes 3 agruments: x is your actual value from the column, y comapare to, z replace by.

    Table.ReplaceValue(#"Changed column type", #date(2015,1,1), null, (x,y,z) => if x < y then z else x, {"TS01_stock_take_date", "TS02_creation_date"})

     

    Kind regards,

    JB

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Arnault_ 

     

    you have to use a Table.TransformColumns. Check out this code (not tested)

    = Table.TransformColumns
(
   #"Merged Columns", 
   {
      {
         "TS01_stock_take_date" ,
         (datecheck)=>
         if datecheck  < #date(2015,1,1) then null else datecheck,
         type date   
      }
   }
)


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • Arnault_'s avatar
      Arnault_
      Resolver III
      6 years ago

      Hi Jimmy801 ,

      Thanks for your reply. Well, I don't see how it answers my issue regarding applying the function over "multiple colums". I mean, I don't understand how your formula works but it does not apply changes over the 2nd column.

      Cheers

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Arnault_ ,

         

        I guess, one option would be to replace value on the other tab as a separate (second) step.

         

        If you something like "mass" change, you need to replace the standard Replacer function, and pass a list of columns to apply to.

        Replacer function takes 3 agruments: x is your actual value from the column, y comapare to, z replace by.

        Table.ReplaceValue(#"Changed column type", #date(2015,1,1), null, (x,y,z) => if x < y then z else x, {"TS01_stock_take_date", "TS02_creation_date"})

         

        Kind regards,

        JB