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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Shakir1
Regular Visitor

Find a value in column of type text and output result as datetime

Hi All,

I've got some data where i need to find the time of "00:00:00" in a column, add a day of +1 to date. Sounds simple but i keep getting errors especially with the value types.

 

This is what ive done below, but it keeps erroring with:

 

"Expression.Error: The Date value must contain the Date component.
Details:
2010-04-01T00:00:00"

 

What ive tried doing here is I've tried looking up all dates that contain "00:00:00" as a text type, then tried changing the results to type datetime, and then adding the +1 to the dates.

 

My code:

if Text.Contains([#"Reading date-time"], "00:00:00") then DateTime.FromText(Date.AddDays([#"Reading date-time"],1)) else [#"Reading date-time"])

 

Any help would be greaty appreciated!

Thank you

 

1 ACCEPTED SOLUTION

Hey nvm, i figured it out. i had the nested functions the wrong way. This works:

 

 if Text.Contains([#"Reading date-time"], "00:00:00") then Date.AddDays(DateTime.FromText([#"Reading date-time"]),1) else [#"Reading date-time"]

View solution in original post

3 REPLIES 3
Shakir1
Regular Visitor

Hi,

Sure heres the sample data:

Hope this helps, i cant seem to scan the column for text and THEN convert that text to a datetime so it can be outputted as a datetime. I just want to find the data in column "reading date-time" where if the field contains "00:00:00" grab it and then add a day to that field. Wonder if theres a more elegant way to approach it?

Thank you for your help so far

Shakir1_1-1662142828979.png

 

 

HotChilli
Super User
Super User

It's not clear what your source looks like and what you are trying to achieve.

I'm reasonably sure that the initial field is text but that means that Date.AddDays can't be used on it because that requires a date or datetime type.

Also the 2 paths from the if..then..else cannot return different datatypes.  At the moment they would return text or datetime, so that won't work.

If you provide some sample data and show the desired result, we might be able to progress this. 

Hey nvm, i figured it out. i had the nested functions the wrong way. This works:

 

 if Text.Contains([#"Reading date-time"], "00:00:00") then Date.AddDays(DateTime.FromText([#"Reading date-time"]),1) else [#"Reading date-time"]

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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