Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power query - if function with dates

I have table with two columns with dates Release date and closed date. I want to create custom column, but I don't know how i should create formula.  Basically I need 3 category 1) if relase date ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can add custom column with the formula as shown in below screenshot, later please adjust the conditions base on your scenario..

    =if [Release date] < #date(2020, 1, 1) then  1
      else if [Release date] > #date(2019, 12, 31) and [Release date] < #date(2021, 1, 1) then 2
      else if [closed date] < #date(2021, 1, 1) then 3
      else  0

    Best Regards

     

     

Angith_Nair's avatar
Angith_Nair
Continued Contributor
5 years ago

Hi Anonymous ,

Create a custom column in Power Query level and write this code..

Custom Column = if [Release date] < #date(2020, 01, 01) then "1" else if [Release date] > #date( 2019, 12, 31) and  [Release date] < #date( 2020, 01, 01) then "2" else if [closed date] < #date (2021, 01, 01) then "3" else "0"

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi, I wrote this code and I have errors in each cell. 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I encountered the same issue. Check if the Data Type of your date columns ([release date], [closed date]) is in "Date". The above doesn't seem to work on "Date/Time" data. I tried it on "Date" data and it worked.