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

[Shakir1]

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

[Shakir1]

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

[HotChilli]

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. 

[Shakir1 · 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"]