Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Powerquery: replace blanks and dates in past with a future date

Hi there

 

I am trying to replace null values with a date in the future (30/12/22) and dates in the past ie. before today with a date in the future (31/12/22). Replacing null values is straightforward but replacing dates in the past is confusing me. I would really appreciate some advice on a solution!

 

Here is what I have:

 

#"Replaced Value" = Table.ReplaceValue(Table1_Table,null,#date(2022, 12, 30),Replacer.ReplaceValue,{"Forecast"}),

 

#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Forecast],each if [Forecast] < DateTime.FixedLocalNow() then #date(2022,12,31) else [Forecast],Replacer.ReplaceValue,{"Forecast"})

 

Many thanks!

 

Replace future dates by today date 

 

 

 

Need Help
  • jeroendekk's avatar
    jeroendekk
    4 years ago

    Hi Anonymous 
    I think your forecast date is probably a date not a datetime. Which mean the comparing it to DateTime.FixedLocalNow() will not work (they need matching datatypes. You could solve this with a Date.From function you could also get both steps in one.

      #"Replace Value" = Table.ReplaceValue(
    #"Table1_Table", 
    each [Forecast], 
    each 
      if [Forecast] < Date.From(DateTime.FixedLocalNow()) or [Forecast] = null then
        #date(2022, 12, 31)
      else
        [Forecast], 
    Replacer.ReplaceValue, 
    {"Forecast"}
  )


    If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

    Best regards,
    Jeroen


  • Anonymous's avatar
    Anonymous
    4 years ago

    Many thanks Jeroen, your advice to introduce a Date.From function worked. I kept my steps seperate in the end as it was a simple fix. 

     

    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(2021, 12, 31),Replacer.ReplaceValue,{"Forecast"}),


    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Forecast],each if [Forecast] < Date.From(DateTime.FixedLocalNow()) then #date(2022,12,30) else [Forecast],Replacer.ReplaceValue,{"Forecast"}),

     

    Regards

    Matt

6 Replies

  • jeroendekk's avatar
    jeroendekk
    Responsive Resident
    4 years ago

    Hi Anonymous 
    I think your forecast date is probably a date not a datetime. Which mean the comparing it to DateTime.FixedLocalNow() will not work (they need matching datatypes. You could solve this with a Date.From function you could also get both steps in one.

      #"Replace Value" = Table.ReplaceValue(
    #"Table1_Table", 
    each [Forecast], 
    each 
      if [Forecast] < Date.From(DateTime.FixedLocalNow()) or [Forecast] = null then
        #date(2022, 12, 31)
      else
        [Forecast], 
    Replacer.ReplaceValue, 
    {"Forecast"}
  )


    If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

    Best regards,
    Jeroen


    • CNENFRNL's avatar
      CNENFRNL
      Community Champion
      4 years ago

      Use coalesce operator to simplify further more,

      Table.ReplaceValue(#"Changed Type", each [Forecast], each if ([Forecast]??Date.From(0)) < Date.From(DateTime.FixedLocalNow()) then #date(2022,12,31) else [Forecast], Replacer.ReplaceValue, {"Forecast"})
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Many thanks Jeroen, your advice to introduce a Date.From function worked. I kept my steps seperate in the end as it was a simple fix. 

       

      #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,#date(2021, 12, 31),Replacer.ReplaceValue,{"Forecast"}),


      #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",each[Forecast],each if [Forecast] < Date.From(DateTime.FixedLocalNow()) then #date(2022,12,30) else [Forecast],Replacer.ReplaceValue,{"Forecast"}),

       

      Regards

      Matt

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Instead of Table.ReplaceValue, you could also try Table.Transform.

     

    Table.TransformColumns(
    Table1_Table,
    {
    "Forecast",
        each if _ = null then #date(2022, 12, 30)
        else if _ > Date.From(DateTime.FixedLocalNow()) then #date(2022, 12, 31)
        else if _ < Date.From(DateTime.FixedLocalNow()) then #date(2022, 12, 30)
        else _
    }
)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi

       

      Like your solution. Only aspect that isn't working is for dates in the future, these should be the [Forecast] value. I tried adjusting your code but no luck! Suspect it would be a simple fix.

      • AlexisOlson's avatar
        AlexisOlson
        Super User
        4 years ago

        If you want the [Forecast] value, then use _ instead of #date(2022, 12, 31).