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

[Anonymous]

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 

[jeroendekk · Accepted solution]

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

[AlexisOlson]

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]

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]

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

[jeroendekk · Accepted solution]

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 · Accepted solution]

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

[CNENFRNL]

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"})