Forum Discussion
How to create a custom column in Power Query for Expiration date of Products?
I have two columns. One column with "Purchase date" & Second column with "Subscription Type" which looks like this:
| Purchase Date | Subscription Type |
| 09/01/2023 | Yearly |
| 15/01/2023 | Monthly |
| 02/02/2023 | Yearly |
| 25/03/2023 | Monthly |
The Output I am expecting for instance for first 2 rows needs to be this (For Yearly, 08/01/2024) & (For Monthly, 15/02/2023).
I want to add a custom column for calculating expiration date based on this two columns, which functions can I use to set it? or If someone can write a function for this particular case, it would be really greatful.
Hi again mosarahit48 🙂
update your formula to :
Date.AddDays( if [Subscription Type] = "Yearly" then Date.AddYears([Purchase Date],1)
else
Date.AddMonths([Purchase Date],1),-1)If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Ritaf1983
Super User
Hi mosarahit48
You can add custom column with formula:
=if [Subscription Type] = "Yearly" then Date.AddYears([Purchase Date],1)
else
Date.AddMonths([Purchase Date],1)Result :
Sample file
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Ritaf1983 Thank you for presenting the solution. I greatly appreciate that.
However, I would like to see the expiration date as 1 day before, for instance: "for Yearly: "09/01/2023" to "08/01/2024" & "for Monthly: "28/02/2023" to "27/03/2023".
Could you further assist me with this.- Ritaf1983
Hi again mosarahit48 🙂
update your formula to :
Date.AddDays( if [Subscription Type] = "Yearly" then Date.AddYears([Purchase Date],1)
else
Date.AddMonths([Purchase Date],1),-1)If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thankyou Ritaf1983 it works, Thank you for the help & support😀
