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 straightforw...
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

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper 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
      Icon for Super User rankSuper User
      4 years ago

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