M Language IF Statement Setting Specific DateTime

[luismiguelopes]

Hi everyone,

I'm trying to do a if statement with this logic behind in M language

if weekday = 5 and Time Opened Hour > 18 then sum 3 days to Time Opened column and set the time to 7.

= Table.AddColumn(#"Filtered Rows", "OutWorkTime", each if [WeekDay] = 5  then Date.AddDays([Time Opened], 3) and DateTime.Time([Time Opened], #time(7,0,0)) 

else null)

Thanks for the help

[Jimmy801 · Accepted solution]

Hello @luismiguelopes 

there are several errors in your code. Try out this and check out what i changed. And by the way, you dont't need to have this calculated columns for this

if Date.DayOfWeek(Date.From([Time opened]))=4 and Time.Hour([Time opened])>18 then DateTime.From(Date.From(Date.AddDays([Time opened],3))) + #duration (0,7,0,0) else [Time opened]

Here the complete solution for everybody

let
	Source = #table
	(
		{"Time opened"},
		{
			{"43868,7916666667"},	{"43868,625"}
		}
	),
    ToDateTime = Table.TransformColumns
    (
        Source,
        {
            {
                "Time opened",
                each DateTime.From(Number.From(_)),
                type datetime
            }
        }
    ),
    AddColumn = Table.AddColumn
    (
        ToDateTime,
        "new column",
        each if Date.DayOfWeek(Date.From([Time opened]))=4 and Time.Hour([Time opened])>18 then DateTime.From(Date.From(Date.AddDays([Time opened],3))) + #duration (0,7,0,0) else [Time opened],
        type datetime
    )
in
	AddColumn

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[nickyvv]

Hi @luismiguelopes,

I think you've mistakenly took DateTime.Time to set the time, but this formula gets the time of your column.

I think you'll have to create your datetime with the date-part of your column [Time Opened], and add the time of 7 hours yourself.

Maybe something like the following:

#datetime(Date.Year([Time Opened]), Date.Month([Time Opened]), Date.Day([Time Opened]), 7, 0, 0)

Did this help you or did I answer your question?
Then please give kudos or mark my post as a solution!
My blog: nickyvv.com
Twitter: @NickyvV