IF in M language for a date and time

[AliceW]

Hello everyone,

This should be easy, yet it's driving me crazy. Any help would be appreciated.

I have this column, 'Sale Date'.

If the date is #date(2024,07,29) AND the hour is 17,

then  #date(2023,12,31)

else 'Sale Date'.

I've tried various options and I keep getting errors. 

Thank you,

Alice

[rajendraongole1]

Hi @AliceW - add a custom column in power query editor,

if [Sale Date] = #datetime(2024, 7, 29, 17, 0, 0) then #date(2023, 12, 31) else [Sale Date]

if you dont require time, you can just remove the time stamp after 29 day as below add timehour for 17th as below:

= if [Sale Date] = #date(2024, 7, 29) and Time.Hour([Sale Time]) = 17 then #date(2023, 12, 31) else [Sale Date]

you can replace sale time with the right column name containing the time information.

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[AliceW]

Hello Rajen,

Thank you. Unfotunately, I get this 'Token eof expected' error. I don't get it... 

Best,

Alice

[v-junyant-msft · Accepted solution]

Hi @AliceW ,

First of all, you have capitalised the ‘i’ in ‘if’, Power Query is case sensitive, if you capitalise it, it will not recognise the ‘if’ function.

Also, you mentioned hours = 17, so I'm guessing your data type is date/time. if it's a date/time type, then you can't use date(2024, 7, 29), but have to split it up to make separate judgements for the year, month and day separately. For example:

if Date.Year([Sale Date]) = 2024 and Date.Month([Sale Date]) = 7 and Date.Day([Sale Date]) = 29 and Time.Hour([Sale Date]) = 17 then #datetime(2023,12,31,0,0,0) else [Sale Date]

If your type is date and time is in another column, then you can use the following code:

if [Sale Date] = #date(2024, 7, 29) and Time.Hour([Sale Time]) = 17 then #date(2023, 12, 31) else [Sale Date]

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.