Forum Discussion

BishwaR's avatar
BishwaR
Helper V
5 years ago
Solved

New Date Column

I need to create a new Product Expiration column in power query ( No Dax)

 

I have an existing Product Expiratioin column but besides that I need to create a new Product Epiration column where all the dates for next 90 days I need to replace one date which is today+90. For example if the existing Product Expiratiion has todays date then in the New Expiration column it has to be today's date+90. It has to replace the existing date with Today+90 for all the dates that fall within 3 months range then rest would be as it is.

 

Thanks

Bishwa

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Table.AddColumn (LastStepName, "Expiration Date", each if [DateColumn] >= Date.AddDays(Date.From(DateTime.LocalNow()), -90) then Date.AddDays(Date.From(DateTime.LocalNow()), +90) else [DateColumn])

     

    --Nate

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Table.AddColumn (LastStepName, "Expiration Date", each if [DateColumn] >= Date.AddDays(Date.From(DateTime.LocalNow()), -90) then Date.AddDays(Date.From(DateTime.LocalNow()), +90) else [DateColumn])

     

    --Nate

      • BishwaR's avatar
        BishwaR
        Helper V
        5 years ago

        I figured out I missed a parenthesis Sorry !

        Thanks for your help This is my solution Yeah

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You need just one parentheses at the end. 

    • BishwaR's avatar
      BishwaR
      Helper V
      5 years ago

      Sorry I just realised it is changing the date for all the Expiration dates I just wanted to change the date for next 90 days only so how to write and && conditiion like   "Expiration Date", each if [DateColumn] >= Date.AddDays(Date.From(DateTime.LocalNow()), -90) &&  "Expiration Date", each if [DateColumn] <= Date.AddDays(Date.From(DateTime.LocalNow()), +90) then Date.AddDays(Date.From(DateTime.LocalNow()), +90) else [DateColumn]) then Date.AddDays(Date.From(DateTime.LocalNow()), +90) else [DateColumn])

      so that all the dates after 90 days will fall under else condition

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    OK, if I understand correctly, for Expiration dates that are today through the next 90 days, use today+90 else use current Expiration date. If so, it's:

     

    Table.AddColumn (LastStepName, "Expiration Date", each if [DateColumn] >= Date.From(DateTime.LocalNow()) and [DateColumn] <=  Date.AddDays(Date.From(DateTime.LocalNow()), + 90) then Date.AddDays(Date.From(DateTime.LocalNow()), +90) else [DateColumn])