Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
troybos
New Member

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @troybos 

 

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,

vjingzhanmsft_2-1706162031767.png

 

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. 

vjingzhanmsft_0-1706161677431.png

 

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

vjingzhanmsft_1-1706161815100.png

 

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

View solution in original post

5 REPLIES 5
tackytechtom
Super User
Super User

Hi @troybos ,

 

I tried your code with an example in Power BIs Power Query and it worked out well:

tackytechtom_0-1706126805759.png

 

Could it be that your Date_example column is of the wrong data type (not a date column)?

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

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.

 

troybos_0-1706139913893.png

troybos_1-1706139946041.png

 

Anonymous
Not applicable

Hi @troybos 

 

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,

vjingzhanmsft_2-1706162031767.png

 

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. 

vjingzhanmsft_0-1706161677431.png

 

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

vjingzhanmsft_1-1706161815100.png

 

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

@Anonymous 

Thank you this worked. 

 

@tackytechtom  You were correct, it was the date type that was wrong.  I have this query merged with another and I had changed the date type to "date" in the merged query but not in this primary query.

 

Thank you all for your assistance.

HotChilli
Super User
Super User

What's the full error message?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.