Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Add days to date in PowerQuery

I need some assistance with an if statement I need to create in Excel Power Query. The issue I am having is adding days to a date in the condition portion of the if statement. I'm not sure how to write that. Here is what I have that doesn't work...
 

 

 

 

if Date.AddDays([Date_example],-30) < #date(2024,7,1)
  then Date.AddDays([Date_example],-44)
  else Date.AddDays([Date_example],-29)

 

 

 

It tells me the code is correct but when I close the code window, I get an error in the column, and it tells me that it can't apply the < operator. Can anyone see what I'm doing wrong? Thanks
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    According to the error message, the error occurs because the data on both sides of  "<" sign are of different types. From the screenshot, it seems you don't change the data type with Power Query, so I guess that you may have changed the date column to date format in Excel, however its values are probably still in datetime type, which may cause this error. For example,

     

    To resolve this error, you can add a step to change the date column to date type in Power Query Editor before adding the custom column. 

     

    In addition, when you add this custom column, modify the last ELSE result to null instead of "".

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      I checked and my Date_Example column is of the type "Date".  I found another error regarding null values but have fixed that and am now back to the operator error.  Here is my current code and the error message I am getting.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous 

         

        According to the error message, the error occurs because the data on both sides of  "<" sign are of different types. From the screenshot, it seems you don't change the data type with Power Query, so I guess that you may have changed the date column to date format in Excel, however its values are probably still in datetime type, which may cause this error. For example,

         

        To resolve this error, you can add a step to change the date column to date type in Power Query Editor before adding the custom column. 

         

        In addition, when you add this custom column, modify the last ELSE result to null instead of "".

         

        Best Regards,
        Jing
        If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!